How to calculate time spent on a project using the Time Function?


I am trying to set up a time tracking sheet to track how our factory staff spend their time. I have "Start Time" and "End Time" fields in 24 hour format. I am trying to calculate the time spent (the difference between the start and end times).

I found the Smartsheet article on the Time Function, however, their formula for converting a time difference into hours and minutes keeps coming up with the wrong minutes.

For example, my "Start Time" is "06:01" and my "End Time" is "10:47". I used the formula example supplied in the article and linked it to the correct columns on my sheet: =ROUNDDOWN((TIME([End Time]@row) - TIME([Start Time]@row)) * 24) + " hour(s) and " + VALUE(RIGHT((TIME([End Time]@row) - TIME([Start Time]@row)) * 24, FIND(".", (TIME([End Time]@row) - TIME([Start Time]@row)) * 24))) * 60 + " minutes"

The formula is coming up with the answer "4 hour(s) and 4020 minutes". The hours are correct, but the minutes are completely wrong.

Can anyone tell me what I have done wrong?


  • bbates
    bbates ✭✭

    This post was super helpful in resolving this issue Calculating Time Worked for Employees

    The poster adds Start and End Date columns of field type Date and a Sum column of field type number. I've modified the formula to fit your hour(s) / minutes text:

    Start Date: =today() assuming this is a daily activity or manually entered date to capture past dates

    End Date: =today() assuming this is a daily activity or manually entered date to capture past dates

    SUM column formula: =((VALUE(LEFT([End Time]@row, FIND(":", [End Time]@row) - 1)) + VALUE(RIGHT([End Time]@row, 2)) / 60) + ([End Date]@row - [Start Date]@row) * 24) - (VALUE(LEFT([Start Time]@row, FIND(":", [Start Time]@row) - 1)) + VALUE(RIGHT([Start Time]@row, 2)) / 60)

    DURATION column formula: =INT(Sum@row) + " hour(s) " + IF((Sum@row - INT(Sum@row)) * 60 < 10, "0") + (Sum@row - INT(Sum@row)) * 60 + " minutes"

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!