Time math error?

abwmaynard

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!

Tim C

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.

jmyzk_cloudsmart_jp

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

https://app.smartsheet.com/b/publish?EQBCT=e595326d18e44573a8948123c4a74fef