Total Time Taken

Lee Wood

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.

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:

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

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:

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.