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

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

  • 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 '12 at 5:22
  • Cheers, I know, I'd rather it be called IndexOf like in Apex. Hopefully it works alright :) – techtrekker Nov 14 '12 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

  • 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 '12 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 '12 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 '12 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))

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.

  • 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 '14 at 22:29
  • 1
    New here, learning the ropes. Sorry for inconvenience. Will make the edits. – AshKing Sep 14 '14 at 23:59

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.