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
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!