I have 3 picklist field "StartMonth","MiddleMonth" and "Endmonth". which as values from january-december for all the 3 fields. i need validation rule to throw error if startmonth=january, middlemonth=september and endmonth should come after middle month and before startmonth. example ---> if endmonth comes inbetween startmonth and middle month it should throw error.


I created 3 new formula fields to get the numeric value for the month.Please find the code below for formula fields.

CASE( StartMonth__c, 
"January", 1, 
"February", 2, 
"March", 3, 
"April", 4, 
"May", 5, 
"June", 6, 
"July", 7, 
"August", 8, 
"September", 9, 
"October", 10, 
"November", 11, 
"December", 12, 

I named these fields as StartMonthValue, MiddleMonthValue and EndMonthValue.

After that, I created validation rule as below to throw an error if End month is in between Start and Middle.

If(StartMonthValue__c < MiddleMonthValue__c , If( EndMonthValue__c >= StartMonthValue__c && 
EndMonthValue__c <= MiddleMonthValue__c , true,false) ,If( (EndMonthValue__c >= StartMonthValue__c && 
EndMonthValue__c >= MiddleMonthValue__c) || (EndMonthValue__c <= StartMonthValue__c && 
EndMonthValue__c <= MiddleMonthValue__c) , true,false))

As your fields as Picklist, we cannot use Date methods to get month value.

I hope this helps. :)

| improve this answer | |

Here you go! Get the month component of the Date and use the operator to find where the end month lies

    If((VALUE(TEXT(MONTH(Endmonth__c))) > VALUE(TEXT(MONTH(startMonth__c)))) && 
(VALUE(TEXT(MONTH(Endmonth__c))) < VALUE(TEXT(MONTH(MiddleMonth__c)))),  true,false)

enter image description here

| improve this answer | |

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.