2

I'm trying to write validation rule that end_date__c should be <= start_date__c

isHired__c is boolean

But does not seems to work, for an example:

my start_date__c = 8/8/2016 11:34 AM
end_date__c = 8/8/2016 11:34 AM

the validation should accept since I'm checking to see if the start <= but I'm keep getting the validation message as if the condition did not match.

here is what I have got:

AND
( 
  (DATEVALUE(start_date__c) - end_date__c) <=1, isHired__c, ISPICKVAL(status__c, 'Approved')
)
2
  • Does you get any error or your validation never executes correctly? Commented Aug 8, 2016 at 16:46
  • 2
    Please specify all involved field types. Why not just EndDate__c <= StartDate__c? No need for arithmetic.
    – Adrian Larson
    Commented Aug 8, 2016 at 16:47

2 Answers 2

2

Start Date minus End Date means that the result would be negative if the End Date was after the Start Date, which should trigger this rule. Instead, you meant to say End Date minus Start Date, which would be negative if the Start Date was after the End Date. Of course, as Adrian said, simply use the comparison operators directly, which helps you avoid that common mistake:

End_Date__c < Start_Sate__c && isHired__c && ISPICKVAL(Status__c, 'Approved')

As, as you've noticed, when doing your math, if Start Date and End Date are the same, you'd also get the error (0 is less than 1).

2
  • I'm wondering what is the best practice to use when it comes to validation rule and I have seen you have used ampersand with multiple check where as I have used start with AND and with commas to compare the other fields. Which pattern should used? I found your approach to be the easiest one 1+.
    – Nick
    Commented Aug 8, 2016 at 17:10
  • @NickKahn They're roughly equivalent, so use whichever you prefer. the "AND" function is geared towards people with a history with Excel formulas, while "&&" is geared more towards developers. I find && and || (OR) easier to read than AND and OR, so that's what I tend to use, especially when you start needing more complicated Boolean expressions.
    – sfdcfox
    Commented Aug 8, 2016 at 17:12
0

I think you may try the below code:

AND ( (end_date__c <= start_date__c, isHired__c, ISPICKVAL(status__c, 'Approved') )

We use DateValue function to return the date value of date/time. For more details on DATEVALUE function refere below URL:

https://help.salesforce.com/apex/HTViewHelpDoc?id=formula_using_date_datetime.htm

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .