1

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.

  • What are the increments, and by similar do you mean equal to or what? – Girbot Sep 14 '14 at 20:39
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.

  • VALUE (LEFT (TEXT(Proposed_Work_Start__c) , FIND(":",TEXT(Proposed_Work_Start__c )) - 1, 2 )) * 60 + VALUE (RIGHT (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, 2 )) * 60 + VALUE (RIGHT (TEXT(Proposed_Work_End__c) , FIND(":",TEXT(Proposed_Work_End__c )) + 1, 2 )) + IF(RIGHT(TEXT(Proposed_Work_End__c), 2 ) = 'PM' , 720 , 0 ) – AshKing Sep 15 '14 at 0:14
  • I added the formula above after fixing a couple of misplaced parentheses but now I am getting this error:Error: Incorrect number of parameters for function 'LEFT()'. Expected 2, received 3 – AshKing Sep 15 '14 at 0:15
  • 1
    I think this was my original problem too as I was not able to provide the parameter to RIGHT to read only 2 places after":" I am not very familiar with Salesforce at this point and really appreciate your taking time to help me. Please see my solution through. – AshKing Sep 15 '14 at 0:17
1

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 ))

  • Haha, well, I'm heading over to Dreamforce... :) – Caspar Harmer Sep 19 '14 at 3:41
  • Not this year but I am sure we will meet some other year in Dreamforce and I will honor my pledge at that time :) – AshKing Sep 20 '14 at 18:16
0

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.

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.