How to calculate time spent on a project using the Time Function?
Hello,
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?
Answers

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
Categories
 All Categories
 14 Welcome to the Community
 10.7K Get Help
 63 Global Discussions
 68 Industry Talk
 385 Announcements
 3.5K Ideas & Feature Requests
 55 Brandfolder
 125 Just for fun
 50 Community Job Board
 464 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives
Check out the Formula Handbook template!