I need help in writing the formula:

I have the following fields in my custom object:

Approve_Date__c - date field 
Start_Date__c - datetime field
Hiring__c - checkbox


If Hiring__c is checked (true) then validate Approve_Date__c should be one day before the Start_Date__c

If Hiring__c is unchecked (false) then no validation fires.

Tried the following:

Hiring__c > DATEVALUE(Start_Date__c ) +1
  • 1
    What does your formula currently look like? Show us and we'll help you correct it.
    – Mark Pond
    Jul 25 '16 at 16:36
  • What error did you get when you tried that value in the formula editor?
    – Mark Pond
    Jul 25 '16 at 16:41
  • @NickKahn if Hiring__c is a checkbox field than how can you compare date result in checkbox?
    – Ricky
    Jul 25 '16 at 16:55
  • @Ricky: Yes you are correct, I updated my question with what I have tried, Hiring__c > DATEVALUE(Start_Date__c ) +1
    – Nick Kahn
    Jul 25 '16 at 16:57

You just need:

    Hiring__c, Approval_Date__c != DATEVALUE(Start_Date__c) + 1

You can't compare any of these fields directly to each other, they're all different types! Using an AND clause starting with the checkbox means you only validate if it is checked, then you just need to convert your Datetime field to a Date value to compare it to what you already have.

  • I totally agree with what you are saying Adrian, but isn't it technically a best practice to state Hiring__c = TRUE or does it not really matter?
    – cmmoutes13
    Jul 25 '16 at 17:44
  • No, I actually think checking BooleanField__c = true is almost as bad as IF(BooleanField__c, true, false). I find it redundant. @cmmoutes13
    – Adrian Larson
    Jul 25 '16 at 18:08
  • 1
    Hmm alright, I was just curious because that is how I do it on a daily basis. I guess it is a bit redundant. @Adrian
    – cmmoutes13
    Jul 25 '16 at 18:10

First you need both fields in same data type, to do this function DATEVALUE() is used and then if the difference between two dates is not equal to one day and Hiring__c field is checked, then validation rule will show the error message.

In your case, you are trying to add integer to date field which is not allowed.

Try this

AND((DATEVALUE(Start_Date__c) - Approve_Date__c) <> 1,Hiring__c)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.