Formula for Duration using Start & End Date & Time?

afelo

Hello,

I would like a formula to calculate Duration (in hours) using the Start Date, End Date, Start Time, and End Time. I would like to keep the Time in 12-Hour format if possible.

Any formula suggestions? I tried using a formula without factoring in the Dates, but the Duration for those highlighted in yellow ended up being incorrect. I would really appreciate any help.

Thank you!

Samuel Mueller

@afelo You are going to have to check this and make sure it's right. Also this implies your time format is always the same ie 00:00AM or 00:00PM. You should be able to copy the below and paste it into your duration column, then make it a column formula.

=IFERROR(ROUND((IF(CONTAINS("AM", [End Time]@row), IF(VALUE(MID([End Time]@row, 1, 2)) = 12, VALUE(MID([End Time]@row, 1, 2)) * 60 + VALUE(MID([End Time]@row, 4, 2)) - 720, VALUE(MID([End Time]@row, 1, 2)) * 60 + VALUE(MID([End Time]@row, 4, 2))), IF(VALUE(MID([End Time]@row, 1, 2)) = 12, VALUE(MID([End Time]@row, 1, 2)) * 60 + VALUE(MID([End Time]@row, 4, 2)) + (12 * 60) - 720, VALUE(MID([End Time]@row, 1, 2)) * 60 + VALUE(MID([End Time]@row, 4, 2)) + (12 * 60))) - IF(CONTAINS("AM", [Start Time]@row), IF(VALUE(MID([Start Time]@row, 1, 2)) = 12, VALUE(MID([Start Time]@row, 1, 2)) * 60 + VALUE(MID([Start Time]@row, 4, 2)) - 720, VALUE(MID([Start Time]@row, 1, 2)) * 60 + VALUE(MID([Start Time]@row, 4, 2))), IF(VALUE(MID([Start Time]@row, 1, 2)) = 12, VALUE(MID([Start Time]@row, 1, 2)) * 60 + VALUE(MID([Start Time]@row, 4, 2)) + (12 * 60) - 720, VALUE(MID([Start Time]@row, 1, 2)) * 60 + VALUE(MID([Start Time]@row, 4, 2)) + (12 * 60))) + (([End Date]@row - [Start Date]@row) * 24 * 60)) / 60, 0) + ":" + MOD((IF(CONTAINS("AM", [End Time]@row), IF(VALUE(MID([End Time]@row, 1, 2)) = 12, VALUE(MID([End Time]@row, 1, 2)) * 60 + VALUE(MID([End Time]@row, 4, 2)) - 720, VALUE(MID([End Time]@row, 1, 2)) * 60 + VALUE(MID([End Time]@row, 4, 2))), IF(VALUE(MID([End Time]@row, 1, 2)) = 12, VALUE(MID([End Time]@row, 1, 2)) * 60 + VALUE(MID([End Time]@row, 4, 2)) + (12 * 60) - 720, VALUE(MID([End Time]@row, 1, 2)) * 60 + VALUE(MID([End Time]@row, 4, 2)) + (12 * 60))) - IF(CONTAINS("AM", [Start Time]@row), IF(VALUE(MID([Start Time]@row, 1, 2)) = 12, VALUE(MID([Start Time]@row, 1, 2)) * 60 + VALUE(MID([Start Time]@row, 4, 2)) - 720, VALUE(MID([Start Time]@row, 1, 2)) * 60 + VALUE(MID([Start Time]@row, 4, 2))), IF(VALUE(MID([Start Time]@row, 1, 2)) = 12, VALUE(MID([Start Time]@row, 1, 2)) * 60 + VALUE(MID([Start Time]@row, 4, 2)) + (12 * 60) - 720, VALUE(MID([Start Time]@row, 1, 2)) * 60 + VALUE(MID([Start Time]@row, 4, 2)) + (12 * 60))) + (([End Date]@row - [Start Date]@row) * 24 * 60)) / 60, 1) * 60, "")

Samuel Mueller

@afelo You are going to have to check this and make sure it's right. Also this implies your time format is always the same ie 00:00AM or 00:00PM. You should be able to copy the below and paste it into your duration column, then make it a column formula.

=IFERROR(ROUND((IF(CONTAINS("AM", [End Time]@row), IF(VALUE(MID([End Time]@row, 1, 2)) = 12, VALUE(MID([End Time]@row, 1, 2)) * 60 + VALUE(MID([End Time]@row, 4, 2)) - 720, VALUE(MID([End Time]@row, 1, 2)) * 60 + VALUE(MID([End Time]@row, 4, 2))), IF(VALUE(MID([End Time]@row, 1, 2)) = 12, VALUE(MID([End Time]@row, 1, 2)) * 60 + VALUE(MID([End Time]@row, 4, 2)) + (12 * 60) - 720, VALUE(MID([End Time]@row, 1, 2)) * 60 + VALUE(MID([End Time]@row, 4, 2)) + (12 * 60))) - IF(CONTAINS("AM", [Start Time]@row), IF(VALUE(MID([Start Time]@row, 1, 2)) = 12, VALUE(MID([Start Time]@row, 1, 2)) * 60 + VALUE(MID([Start Time]@row, 4, 2)) - 720, VALUE(MID([Start Time]@row, 1, 2)) * 60 + VALUE(MID([Start Time]@row, 4, 2))), IF(VALUE(MID([Start Time]@row, 1, 2)) = 12, VALUE(MID([Start Time]@row, 1, 2)) * 60 + VALUE(MID([Start Time]@row, 4, 2)) + (12 * 60) - 720, VALUE(MID([Start Time]@row, 1, 2)) * 60 + VALUE(MID([Start Time]@row, 4, 2)) + (12 * 60))) + (([End Date]@row - [Start Date]@row) * 24 * 60)) / 60, 0) + ":" + MOD((IF(CONTAINS("AM", [End Time]@row), IF(VALUE(MID([End Time]@row, 1, 2)) = 12, VALUE(MID([End Time]@row, 1, 2)) * 60 + VALUE(MID([End Time]@row, 4, 2)) - 720, VALUE(MID([End Time]@row, 1, 2)) * 60 + VALUE(MID([End Time]@row, 4, 2))), IF(VALUE(MID([End Time]@row, 1, 2)) = 12, VALUE(MID([End Time]@row, 1, 2)) * 60 + VALUE(MID([End Time]@row, 4, 2)) + (12 * 60) - 720, VALUE(MID([End Time]@row, 1, 2)) * 60 + VALUE(MID([End Time]@row, 4, 2)) + (12 * 60))) - IF(CONTAINS("AM", [Start Time]@row), IF(VALUE(MID([Start Time]@row, 1, 2)) = 12, VALUE(MID([Start Time]@row, 1, 2)) * 60 + VALUE(MID([Start Time]@row, 4, 2)) - 720, VALUE(MID([Start Time]@row, 1, 2)) * 60 + VALUE(MID([Start Time]@row, 4, 2))), IF(VALUE(MID([Start Time]@row, 1, 2)) = 12, VALUE(MID([Start Time]@row, 1, 2)) * 60 + VALUE(MID([Start Time]@row, 4, 2)) + (12 * 60) - 720, VALUE(MID([Start Time]@row, 1, 2)) * 60 + VALUE(MID([Start Time]@row, 4, 2)) + (12 * 60))) + (([End Date]@row - [Start Date]@row) * 24 * 60)) / 60, 1) * 60, "")

afelo

That worked perfectly! Thank you so much for your help!

Samuel Mueller

Happy to help!

Glen Harwick

What if you wish to calculate total hours worked for a single day with a start time and end time using the same 12-hour format? I have been unable to figure out how to modify the formula provided to achieve the desired outcome.

Samuel Mueller

@Glen Harwick you should now be able to use the time function to achieve this easier.

=(TIME(endtime@row)-TIME(starttime@row))*24