Formula for Duration using Start & End Date & Time?

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!

Find more posts tagged with

Formulas

Time Calculation

Accepted answers

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

All comments

Samuel Mueller

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.