Formula field for the Difference between 2 time values in picklists

Question

I am very new to Salesforce and have to write a formula field based validation. I have been trying but so far haven't been able to write a correct formula.

My requirement is as follows:

I have 2 picklist fields: Arrival window start and Arrival window end:

Arrival window start has values 7:00 AM to 7:00 PM and Arrival window end has values 7:00 AM to 7:30 PM

I have to run a validation to make sure that the arrival window end is not similar to or less than arrival window start.

Any help will be very highly appreciated. Thanks.

I want to make one validation rule to check AM values and another to check PM values. I already have 2 validation rules which check if start time is PM then end time can't be AM and visa-versa.

I can successfully validate complete hours now but I need to integrate half hour intervals also. I need to verify if start time is 9:30 AM then end time is not selected as 9:00 AM. I need help to extract the minute part from the picklist value without AM/PM.

So far I have been able to do the following:

AND( RIGHT( TEXT(Proposed_Work_Start__c ),2)='AM', RIGHT( TEXT(Proposed_Work_End__c ),2)='AM',

VALUE(LEFT(TEXT(Proposed_Work_Start__c ), FIND(":",TEXT(Proposed_Work_Start__c ))-1)) > VALUE(LEFT(TEXT(Proposed_Work_End__c ), FIND(":",TEXT(Proposed_Work_End__c ))-1)) )

The above works fine where the time is different in hour (IE 9AM -10 AM works) but (9:30-9:00 doesn't work.) The interval are half hours.

Answer 1

EDIT - Changed to use MID

You just have to add the right side also, like:

9:45 PM would evaluate to:

VALUE (MID (TEXT(Proposed_Work_Start__c) , FIND(":",TEXT(Proposed_Work_Start__c )) - 3, 2 )) * 60 + 
VALUE (MID (TEXT(Proposed_Work_Start__c) , FIND(":",TEXT(Proposed_Work_Start__c )) + 1, 2 )) + 
IF( (RIGHT(TEXT(Proposed_Work_Start__c), 2 ) = 'PM' , 720 , 0 )

= 1305

The above also takes into account AM/PM - which means that your formula is going to get quite big. You might want to consider using a workflow to evaluate the time as raw minutes and have your calc just compare the minute values.

Also, I noticed that you are evaluating if either or both of the values have the string 'AM' - you shouldn't need to worry about this if you convert to numbers - a basic comparison will be enough.

Answer 2

The formula you suggested didn't work as it is because the MID was failing in taking the left side out of : sign as the time is saved as 7:00 AM and not as 07:00 AM but a combination of your and my formula worked fine:

(VALUE(LEFT(TEXT(Proposed_Work_Start__c ), FIND(":",TEXT(Proposed_Work_Start__c ))-1)) * 60 + VALUE (MID (TEXT(Proposed_Work_Start__c) , FIND(":",TEXT(Proposed_Work_Start__c )) + 1, 2 )) + IF( RIGHT(TEXT(Proposed_Work_Start__c), 2 ) = 'PM' , 720 , 0 ))>=

(VALUE(LEFT(TEXT(Proposed_Work_End__c ), FIND(":",TEXT(Proposed_Work_End__c ))-1)) * 60 + VALUE (MID (TEXT(Proposed_Work_End__c) , FIND(":",TEXT(Proposed_Work_End__c )) + 1, 2 )) + IF( RIGHT(TEXT(Proposed_Work_End__c), 2 ) = 'PM' , 720 , 0 ))

Answer 3

You can find the answer here :

Formula field for the Difference between 2 time values

In the above post, everything is converted to 24 hour format and to minutes, and then divided by 60. I think you don't need to divide by 60, as long as difference between end time and start time is positive, It should work.