I am trying to write a formula field that calculates the difference between 2 time values. The time inputs are picklist values that are in the format h:mm aa some valid values are
- 12:00 AM
- 9:30 AM
- 6:15 PM
How can I get the number of hours as a decimal between 2 inputs. For example the difference between 9:30 AM and 6:15 PM is 8.75
Attribution to: Daniel Blackhall
Possible Suggestion/Solution #1
Use the LEFT, RIGHT and FIND functions to grab the hours and minutes. Use the VALUE function to convert the strings to numbers. Multiply hours by 60 and add to minutes.
So 9.30 becomes 540 + 30 = 570
If PM convert to 24 hour equivalent (I.e add 12 to hours) 6.15 = 18 x 60 + 15 = 1095
Then subtract to get the difference and divide by 60
1095-570= 525 / 60 = 8.75
Attribution to: techtrekker
Possible Suggestion/Solution #2
To calculate the difference between two decimal values, techtrekkers solution would work good. Just in case someone is looking to find a difference between two date time fields here is the how you do it
(End_Time__c - Start_Time__c) * 1440
The difference would return the NN days format you would need to convert it to NNNN minutes and multiply the value by 1440. This 1440 is the no of minutes in a day
Attribution to: Prady
Possible Suggestion/Solution #3
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.
Attribution to: AshKing
Possible Suggestion/Solution #4
Please tried with the below expression then you can get results difference in minutes.
VALUE(LEFT(LEFT(TEXT(End_Time__c),4), 2))*60 + VALUE(RIGHT(LEFT(TEXT(End_Time__c),5), 2)) - VALUE(LEFT(LEFT(TEXT(Start_Time__c),4), 2))*60 + VALUE(RIGHT(LEFT(TEXT(Start_Time__c),5), 2))
Attribution to: Rajesh Sahu
This content is remixed from stackoverflow or stackexchange. Please visit https://salesforce.stackexchange.com/questions/4381