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!

Best Answer

  • Samuel Mueller
    Samuel Mueller Overachievers
    edited 04/11/23 Answer ✓
    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, "")

Answers

  • Samuel Mueller
    Samuel Mueller Overachievers
    edited 04/11/23 Answer ✓
    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, "")

  • afelo
    afelo ✭✭
    Options

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

  • Samuel Mueller
    Samuel Mueller Overachievers
    Options

    Happy to help!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!