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

Amy A

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:

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

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

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 :)