Total Time Taken

Good afternoon all,

Here we are again, another question about summing up time. I have trawled the forums and cannot find an answer that helps me specifically so I'm hoping that reaching out on here will do so.

I have 2 columns - Start Time and Finish Time, the time is put into each in HH:MM format. I would like another column calculating the total time taken - this will typically be no more than 3 to 4 hours and won't happen overnight when time changes from 23h to 00h.

Thank you for your help in advance.

Find more posts tagged with

Accepted answers

Bassam Khalil

Hi @Lee Wood

Hope you are fine, please create the following helper column and use the following formulas:

the Sample sheet link to test

1- Hours = IF(LEFT([Finish Time]@row, 2) < LEFT([Start Time]@row, 2), (24 + VALUE(LEFT([Finish Time]@row, 2))) - VALUE(LEFT([Start Time]@row, 2)), VALUE(LEFT([Finish Time]@row, 2)) - VALUE(LEFT([Start Time]@row, 2)))

2- Checked Hours =IF(VALUE(MID([Finish Time]@row, 4, 2)) < VALUE(MID([Start Time]@row, 4, 2)), VALUE(Hours@row - 1), Hours@row)

3- Minutes =IF(VALUE(MID([Finish Time]@row, 4, 2)) < VALUE(MID([Start Time]@row, 4, 2)), VALUE(60) + VALUE(MID([Finish Time]@row, 4, 2)) - VALUE(MID([Start Time]@row, 4, 2)), VALUE(MID([Finish Time]@row, 4, 2)) - VALUE(MID([Start Time]@row, 4, 2)))

4- total time taken =IF([Checked Hours]@row < 10, "0" + [Checked Hours]@row + ":" + Minutes@row, [Checked Hours]@row + ":" + Minutes@row)

the following screenshot shows thesample results:

All comments

Antonio Figueroa

You have come to the right place. I have a work around but dont know if you are going to like it.

It is hard to explain but I will share a sheet with you that I use.

Please let me know when you are done with the sheet so I can Delete.

When Entering times please use the dropdown list. This will calculate any hours no matter the time.

There are a lot of hidden columns. So if this works for you let me know.

https://app.smartsheet.com/b/publish?EQBCT=244f98895ae4417199b9d6bade05a833

Bassam Khalil

Hi @Lee Wood

Hope you are fine, please create the following helper column and use the following formulas:

the Sample sheet link to test

2- Checked Hours =IF(VALUE(MID([Finish Time]@row, 4, 2)) < VALUE(MID([Start Time]@row, 4, 2)), VALUE(Hours@row - 1), Hours@row)

4- total time taken =IF([Checked Hours]@row < 10, "0" + [Checked Hours]@row + ":" + Minutes@row, [Checked Hours]@row + ":" + Minutes@row)

the following screenshot shows thesample results:

Lee Wood

Morning all,

I'm using these formulas to track overtime and they are working great, but I would also like a column that deducts unpaid lunch breaks of 30 minutes to be included in the total hours. Is this possible?

Thank you.

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.