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
 Smartsheet Customer Resources
 62.5K Get Help
 367 Global Discussions
 202 Industry Talk
 432 Announcements
 4.4K Ideas & Feature Requests
 137 Brandfolder
 129 Just for fun
 128 Community Job Board
 447 Show & Tell
 29 Member Spotlight
 1 SmartStories
 286 Events
 37 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!