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.
Best Answer
-
Hi @Lee Wood
Hope you are fine, please create the following helper column and use the following formulas:
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:
bassam.khalil2009@gmail.com
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"
Answers
-
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
-
Hi @Lee Wood
Hope you are fine, please create the following helper column and use the following formulas:
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:
bassam.khalil2009@gmail.com
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"
-
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.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 221 Industry Talk
- 464 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 61 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!