Time math error?
I work with a company that provides shore power to cruise ships at several ports across the country. We need to calculate duration of scheduled ship connections to accurately track power used based off of schedules given to us.
I used this formula to calculate duration in hours, but for some reason, random rows were giving me incorrect results, despite the criteria matching other sources that gave me the correct result. There are no other existing formulas in my sheet that would interfere. Has this happened to anyone else? I just removed the formula from the entire column and manually changed the information.
Formula used:
=((VALUE(LEFT([ETD]@row, FIND(":", [ETD]@row) - 1)) + VALUE(RIGHT([ETD]@row, 2)) / 60) + ([Departure]@row - [Arrival]@row) * 24) - (VALUE(LEFT([ETA]@row, FIND(":", [ETA]@row) - 1)) + VALUE(RIGHT([ETA]@row, 2)) / 60)
Results:
Any help would be greatly appreciated!
Answers
-
Take a look at this video from a Smartsheet partner going over the new =TIME( function.
And lots of examples in this post about the use of the time function as well.
-
Hi @abwmaynard
Your formula looks fine, except the duration should be normally Arrival time - Departure time, so I changed the formula as follows;
=(VALUE(LEFT(ETA@row, FIND(":", ETA@row) - 1)) + VALUE(RIGHT(ETA@row, 2)) / 60) - ((VALUE(LEFT(ETD@row, FIND(":", ETD@row) - 1)) + VALUE(RIGHT(ETD@row, 2)) / 60)) + (Arrival@row - Departure@row) * 24
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!