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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!