6

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

Improve this question

5 Answers 5

Reset to default
5

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

Improve this answer
2
  • I was so close! There is enormous irony in the fact I could not find the FIND function. Thanks for the tips techtrekker
    – Daniel Blackhall
    Nov 14, 2012 at 5:22
  • Cheers, I know, I'd rather it be called IndexOf like in Apex. Hopefully it works alright :)
    – techtrekker
    Nov 14, 2012 at 5:25
1

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

Improve this answer
3
  • Hi Prady, I appreciate your effort but I don't think this is a good answer. If someone is looking for the answer using DateTime fields they should post a new question about it (and then you should answer that question)
    – Daniel Blackhall
    Nov 14, 2012 at 5:12
  • point taken.. would it be appropriate if i delete the answer? This might require the community to vote for deleting
    – Prady
    Nov 14, 2012 at 7:27
  • I recommend deletion (so there's no chance of someone downvoting you). You are able to delete your own answer without the community voting, I have done it before when I was in a similar situation.
    – Daniel Blackhall
    Nov 14, 2012 at 20:52
1

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

Improve this answer
1

Hope this will help

(End Time Field API - Start Time Field API ) / 3600000
Improve this answer
1
  • 1 Hr = 60 mins 1 min = 60 secs 1 Sec = 1000 Milliseconds so (Difference in milli seconds) / (1000*60*60) –
    – Lakshay Gandhi
    Aug 12, 2021 at 4:09
0

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.

Improve this answer
2
  • 1
    Hi Ashish, I noticed that you have already asked this question - there is no need to ask it twice. Also, you have provided a question as an answer - only answers go down in this area. If you want to say something, leave a comment. If the answer on the original question solves your problem, you then mark it as accepted and can also upvote it.
    – Caspar Harmer
    Sep 14, 2014 at 22:29
  • 1
    New here, learning the ropes. Sorry for inconvenience. Will make the edits.
    – AshKing
    Sep 14, 2014 at 23:59

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .