Calculating the total duration from a date & military time start and end

I have been looking at a lot of information on this site and this is the closest one I have found, however it does not calculate the minutes correctly. For example instead of 8hrs and 45 min it gives me 9hrs and -15min.

Formula:

=(([On-Site Date]@row - [Notification Date]@row) * 24) + VALUE(LEFT([Time of Crew arrival]@row, 2)) - VALUE(LEFT([Time of notification(military format, e.g. 14:00)]@row, 2)) + " hour(s) and " + (VALUE(RIGHT([Time of Crew arrival]@row, 2)) - VALUE(RIGHT([Time of notification(military format, e.g. 14:00)]@row, 2))) + " min."

Snap shot of columns used and above formula in my sheet:

Find more posts tagged with

Accepted answers

Darren Mullen

@Amy A You can leverage the TIME() function to make your formulas easier. It's doable, but takes a little bit of time to develop. We actually went over time calculations like this in the Smartsheet Guru Elite workshop today!

Here is Smartsheet's function article:

https://help.smartsheet.com/function/time

I do have a video I made a while back about the TIME() function that may help you some. I may have made the formula more complicated than needed, but it works:

https://www.youtube.com/watch?v=hYeIchKhMsc

All comments

Darren Mullen

Here is Smartsheet's function article:

https://help.smartsheet.com/function/time

I do have a video I made a while back about the TIME() function that may help you some. I may have made the formula more complicated than needed, but it works:

https://www.youtube.com/watch?v=hYeIchKhMsc

Amy A

Thanks! This got me most of the way there, I just need to do the minutes now! Appreciate you sharing.

Darren Mullen

@Amy A Great! You're welcome!

Happy to help :)

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.