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!