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?

Find more posts tagged with

Time Calculation

timekeeping

Accepted answers

All comments

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"

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.