Find your content:

Search form

You are here

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

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

My Block Status

My Block Content