# Formula for Duration using Start & End Date & Time?

Options
✭✭

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!

Tags:

• Overachievers
Options

@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, "")

• Overachievers
Options

@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, "")

• ✭✭
Options

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

• Overachievers
Options

Happy to help!

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!