Time Duration Calculation

edited 12/09/19 in Formulas and Functions

I've seen a few ideas on similar topic threads but none of them worked quite right, so I'm trying again!

I have a form staff fill out to record their overtime and the reason for it. Does anyone have any formulas that I can use to populate a column showing the numbers of hours:minutes of overtime based on the "Scheduled Out Time" and "Actual Out Time" that staff members enter into the form?


Thanks much!



  • Nick Burrus
    Nick Burrus ✭✭✭✭✭✭

    This is what I use:

    =VALUE(LEFT([Time Alotted Hours]2, LEN([Time Alotted Hours]2) - 3)) + VALUE(RIGHT([Time Alotted Hours]2, 2)) / 60

    Basically I have a column caled "Time Alotted Hours" of which it has a value like 1:30 in it for 1 hr 30 mins. 

    Basically it takes the left most number (the 1 of 1:30) and converts it to a decimal, here 1.5

    Then it adds the Right Most value (of 2 digits) and divides it by 60 to get a numerical decimal value. 

    Together you get your conversion.

    Do the rest of your formulas using standard decimals for math.

    Dr. St Nicholas Burrus DHA, PMP

    I build Smartsheets for the US Government, State Government, and about a dozen of the US Fortune 100s.

  • Makes sense, and I may need to change my expectations of what data is input to simplify things. 

    I'm hoping for a way to calculate the difference between 5:15 and 4:45 as being 00:30. I'd rather not have staff try and do the math for what portion of an hour they've gone into OT, and just enter their scheduled and actual times as the clock reads.

    I've added a screen shot of the couple of columns giving me trouble below in case that helps.





    SmartSheet Formula.jpg

  • Ezra
    Ezra ✭✭✭

    Could this help you out? One form field each for hours and minutes, beginning and end... but might work until they solve the need for a time formatted column type.


  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Here is how I would set things up...


    Convert everything to minutes in the day, subtract scheduled from actual, convert the difference into hh:mm.




    Come see me at ENGAGE 2023! I have "Happy to help 👍️" buttons!

    If you can't find me roaming the floor, I will most likely be at the 10xViz Partner Booth.

  • @NBurrus - trying to use ur calc for time but getting an error. I have people entering time as 9:01 start time and 11:12 as stop time - this is really 901 am and 1112 am - so I want 2.11 - not getting that Any help is appreciated.


  • @Ezra Your example does not take into account if the start time is PM (say 11:50 PM - 23:50 PM) and the end time is AM (say 1:50 AM) - even in military time, it doesn't work.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @James Carter Feel free to browse through the time based solutions provided HERE. If you are unable to find one that works or need help modifying one to your exact needs, you can start a new thread and then "@mention" me, and I will be happy to help.


    Come see me at ENGAGE 2023! I have "Happy to help 👍️" buttons!

    If you can't find me roaming the floor, I will most likely be at the 10xViz Partner Booth.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!