# Formula field for the Difference between 2 time values

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

# 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

# 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

# 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.

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