Total Time Taken

Options
✭✭✭

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.

• ✭✭✭✭✭✭
Options

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:

bassam.khalil2009@gmail.com

• ✭✭✭✭✭✭
Options

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.

• ✭✭✭✭✭✭
Options

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:

bassam.khalil2009@gmail.com

• ✭✭✭
Options

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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!