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:
Best Answer
-
@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:
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:
Darren Mullen, join the Smartsheet Guru Elite
Get my 7 Smartsheet tips here
Author of: Smartsheet Architecture Solutions
Answers
-
@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:
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:
Darren Mullen, join the Smartsheet Guru Elite
Get my 7 Smartsheet tips here
Author of: Smartsheet Architecture Solutions
-
Thanks! This got me most of the way there, I just need to do the minutes now! Appreciate you sharing.
-
@Amy A Great! You're welcome!
Happy to help :)
Darren Mullen, join the Smartsheet Guru Elite
Get my 7 Smartsheet tips here
Author of: Smartsheet Architecture Solutions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!