5

How can I calculate duration of time between two fields using only HH MM SS? I would need two fields that operator would plug in HH MM SS and have duration calculated into a third field in format that clearly indicates how long the operation took. Any help would be greatly appreciated.

  • Are your two source fields datetime fields? – greenstork Oct 11 '13 at 17:11
  • mast0r...Outstanding answer. It met the bill perfectly. Thanks so much! FYI, in order to ensure data integrity, I did add a validation rule for each Time field to ensure they did use the colon properly. Here was my formula for the rule, followed by the error message. Formula: NOT( REGEX( Time1__c , "(((\\d{2}:){2}\\d{2}))")) Error Message: Time must include a colon (:) between each set of numbers. I.e. 11:11:11 for 11 hours, 11 minute, 11 seconds. Thanks again...! – user4108 Oct 12 '13 at 22:02
4

Yes it is possible with a formula field. Create two text fields Text1 and Text2 where times are saved in form 11:30:00 (hour:minutes:seconds). Then create a new formula field of type Number with two decimal places:

((DATETIMEVALUE("2010-01-01 " + Time2__c) - DATETIMEVALUE("2010-01-01 " + Time1__c))*1440)

This formula uses these times and created two datetime values from it. And then just calculated the duration:

enter image description here

  • 1
    mast0r...Outstanding answer. It met the bill perfectly. Thanks so much! FYI, in order to ensure data integrity, I did add a validation rule for each Time field to ensure they did use the colon properly. Here was my formula for the rule, followed by the error message. Formula: NOT( REGEX( Time1__c , "(((\\d{2}:){2}\\d{2}))")) Error Message: Time must include a colon (:) between each set of numbers. I.e. 11:11:11 for 11 hours, 11 minute, 11 seconds. Thanks again...! – user4108 Oct 12 '13 at 22:02
3

Why not use DateTime? If you go over 24 hours for an operation, wouldn't "13 days 3 hours" be easier to work with than "315 hours"? Your formula field can do the math (difference) then format in whatever fashion you'd like to see...

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.