How to calculate positive & negative time

Carmen Mansfield

Good day everyone,

I've designed a smartsheet for an airline operation and dealing with standard departure / arrival times versus estimated departure / arrival times.

I have this formula that works great: =IF([ETA/ATA (LT)]@row <> "", ((VALUE(LEFT([ETA/ATA (LT)]@row, 2)) + IF([ETA/ATA (LT)]@row < [STA (LT)]@row, 24)) + (VALUE(RIGHT([ETA/ATA (LT)]@row, 2)) / 60)) - (VALUE(LEFT([STA (LT)]@row, 2)) + (VALUE(RIGHT([STA (LT)]@row, 2)) / 60))) * 60

However I'm trying to find a solution to when we've delayed a flight and are arriving earlier than expected without going into the 24 hour clock. Currently it does the +24 which is excellent when we arrive at 03:00, the above formula calculates properly however if we have a scheduled arrival at 16:30 and an estimated arrival at 16:28, it gives us 1438 minutes instead of -2.

I know this formula would work to get the -2 =IF([ETA/ATA (LT)]@row > "", ((VALUE(LEFT([ETA/ATA (LT)]@row, 2)) + IF([ETA/ATA (LT)]@row > [STA (LT)]@row, 24)) + (VALUE(RIGHT([ETA/ATA (LT)]@row, 2)) / 60)) - (VALUE(LEFT([STA (LT)]@row, 2)) + (VALUE(RIGHT([STA (LT)]@row, 2)) / 60))) * 60 however am unsure how to combine it to include both scenarios and have it function properly to calculate an early arrive with negative numbers and arrivals after midnight for positive numbers.

I would appreciate any help and expertise on this to figure it out as I'm a bit lost.

Carmen Mansfield

Found a solution, in case it'll help anyone else out.

=IF([ETA/ATA (LT)]@row > [STA (LT)]@row, IF(VALUE(LEFT([ETA/ATA (LT)]@row,2)) < VALUE(LEFT([STA (LT)]@row,2)), ((24+VALUE(LEFT([ETA/ATA (LT)]@row,2)))*60 + VALUE(RIGHT([ETA/ATA (LT)]@row,2))) - ((VALUE(LEFT([STA (LT)]@row,2))*60) + VALUE(RIGHT([STA (LT)]@row,2))),((VALUE(LEFT([ETA/ATA (LT)]@row,2)))*60 + VALUE(RIGHT([ETA/ATA (LT)]@row,2))) - ((VALUE(LEFT([STA (LT)]@row,2))*60) + VALUE(RIGHT([STA (LT)]@row,2)))), IF(VALUE(LEFT([ETA/ATA (LT)]@row,2)) >= VALUE(LEFT([STA (LT)]@row,2)),((VALUE(LEFT([ETA/ATA (LT)]@row,2)))*60 + VALUE(RIGHT([ETA/ATA (LT)]@row,2))) - ((VALUE(LEFT([STA (LT)]@row,2))*60) + VALUE(RIGHT([STA (LT)]@row,2))), ((24+VALUE(LEFT([ETA/ATA (LT)]@row,2)))*60 + VALUE(RIGHT([ETA/ATA (LT)]@row,2))) - ((VALUE(LEFT([STA (LT)]@row,2))*60) + VALUE(RIGHT([STA (LT)]@row,2)))))

Carmen Mansfield

Found a solution, in case it'll help anyone else out.

=IF([ETA/ATA (LT)]@row > [STA (LT)]@row, IF(VALUE(LEFT([ETA/ATA (LT)]@row,2)) < VALUE(LEFT([STA (LT)]@row,2)), ((24+VALUE(LEFT([ETA/ATA (LT)]@row,2)))*60 + VALUE(RIGHT([ETA/ATA (LT)]@row,2))) - ((VALUE(LEFT([STA (LT)]@row,2))*60) + VALUE(RIGHT([STA (LT)]@row,2))),((VALUE(LEFT([ETA/ATA (LT)]@row,2)))*60 + VALUE(RIGHT([ETA/ATA (LT)]@row,2))) - ((VALUE(LEFT([STA (LT)]@row,2))*60) + VALUE(RIGHT([STA (LT)]@row,2)))), IF(VALUE(LEFT([ETA/ATA (LT)]@row,2)) >= VALUE(LEFT([STA (LT)]@row,2)),((VALUE(LEFT([ETA/ATA (LT)]@row,2)))*60 + VALUE(RIGHT([ETA/ATA (LT)]@row,2))) - ((VALUE(LEFT([STA (LT)]@row,2))*60) + VALUE(RIGHT([STA (LT)]@row,2))), ((24+VALUE(LEFT([ETA/ATA (LT)]@row,2)))*60 + VALUE(RIGHT([ETA/ATA (LT)]@row,2))) - ((VALUE(LEFT([STA (LT)]@row,2))*60) + VALUE(RIGHT([STA (LT)]@row,2)))))

Genevieve P.

Hi @Carmen Mansfield

Thanks for posting your solution! 🙂

Carmen Mansfield

This fomula almost works however it seems that when our STA is before midnight and our ETA is after midnight, it produces a negative result instead a positive result, does anyone know how to fix that?

=IF([ETA/ATA (LT)]@row > [STA (LT)]@row, IF(VALUE(LEFT([ETA/ATA (LT)]@row, 2)) < VALUE(LEFT([STA (LT)]@row, 2)), ((24 + VALUE(LEFT([ETA/ATA (LT)]@row, 2))) * 60 + VALUE(RIGHT([ETA/ATA (LT)]@row, 2))) - ((VALUE(LEFT([STA (LT)]@row, 2)) * 60) + VALUE(RIGHT([STA (LT)]@row, 2))), ((VALUE(LEFT([ETA/ATA (LT)]@row, 2))) * 60 + VALUE(RIGHT([ETA/ATA (LT)]@row, 2))) - ((VALUE(LEFT([STA (LT)]@row, 2)) * 60) + VALUE(RIGHT([STA (LT)]@row, 2)))), IF(VALUE(LEFT([ETA/ATA (LT)]@row, 2)) = VALUE(LEFT([STA (LT)]@row, 2)), IF(VALUE(RIGHT([ETA/ATA (LT)]@row, 2)) < VALUE(RIGHT([STA (LT)]@row, 2)), -((VALUE(LEFT([STA (LT)]@row, 2)) * 60 + VALUE(RIGHT([STA (LT)]@row, 2))) - ((VALUE(LEFT([ETA/ATA (LT)]@row, 2))) * 60 + VALUE(RIGHT([ETA/ATA (LT)]@row, 2)))), ((VALUE(LEFT([ETA/ATA (LT)]@row, 2))) * 60 + VALUE(RIGHT([ETA/ATA (LT)]@row, 2))) - ((VALUE(LEFT([STA (LT)]@row, 2)) * 60) + VALUE(RIGHT([STA (LT)]@row, 2)))), IF(VALUE(LEFT([ETA/ATA (LT)]@row, 2)) >= VALUE(LEFT([STA (LT)]@row, 2)), ((VALUE(LEFT([ETA/ATA (LT)]@row, 2))) * 60 + VALUE(RIGHT([ETA/ATA (LT)]@row, 2))) - ((VALUE(LEFT([STA (LT)]@row, 2)) * 60) + VALUE(RIGHT([STA (LT)]@row, 2))), IF(OR(VALUE(LEFT([STA (LT)]@row, 2)) >= 23, VALUE(LEFT([STA (LT)]@row, 2)) <= VALUE(LEFT([ETA/ATA (LT)]@row, 2))), ((24 + VALUE(LEFT([ETA/ATA (LT)]@row, 2))) * 60 + VALUE(RIGHT([ETA/ATA (LT)]@row, 2))) - ((24 + VALUE(LEFT([STA (LT)]@row, 2))) * 60 + VALUE(RIGHT([STA (LT)]@row, 2))), ((VALUE(LEFT([ETA/ATA (LT)]@row, 2))) * 60 + VALUE(RIGHT([ETA/ATA (LT)]@row, 2))) - ((VALUE(LEFT([STA (LT)]@row, 2)) * 60) + VALUE(RIGHT([STA (LT)]@row, 2)))))))

Genevieve P.

Hi @Carmen Mansfield

The formula won't be able to tell if you're looking at midnight on the current day or midnight on the next day as there are no dates associated with the times.

For example, if your STA (LT) is at 23:59, but your ETA/ATA (LT) is 00:29, the formula is currently assuming that the flight was 23.5 hours early, looking at the same day, instead of being 30min late on to the next day.

Do you have other data in the sheet that records the day along with the time? It would be helpful to see how your sheet is set up, but please block out sensitive data.

Cheers,

Genevieve

Carmen Mansfield

Hi Genevieve,

There's a Date column that'd be for the departure date but not an arrival date column.

Once I'm back in the office I'll be able to get a screenshot of the design of the smartsheet.

I appreciate your help and expertise.

Carmen

Carmen Mansfield

Here is a screenshot of the columns we have in this Smartsheet. Certainly appreciate any help to get it to work for all scenarios.

Genevieve P.

Hi @Carmen Mansfield

I'm not sure that there is a way to do this accurately for each row without having an Date associated with each of your time columns; taking a look at Paul's "Formulas for Calculating Time" thread, I notice he uses Date columns to check if the time has crossed over to the next day or not.

You could perhaps add a checkbox column into the sheet to indicate if the Date ended up being next day instead of same day? Then use that in your formula to adjust how the values are being calculated.

If you have a minute, please add your vote and voice to this Product Ideas post asking for Smartsheet to work with time!

Cheers,

Genevieve

Carmen Mansfield

Thank you Genevieve.

I found a solution by adding in other columns as I couldn't find a solution within that thread:

=IF(AND([Arrival Date]@row = [Actual Arrival Date]@row, [ETA/ATA (LT)]@row < [STA (LT)]@row), (VALUE(LEFT([ETA/ATA (LT)]@row, 2)) * 60 + VALUE(RIGHT([ETA/ATA (LT)]@row, 2))) - (VALUE(LEFT([STA (LT)]@row, 2)) * 60 + VALUE(RIGHT([STA (LT)]@row, 2))), IF(AND([Arrival Date]@row = [Actual Arrival Date]@row, [ETA/ATA (LT)]@row >= [STA (LT)]@row), (VALUE(LEFT([ETA/ATA (LT)]@row, 2)) * 60 + VALUE(RIGHT([ETA/ATA (LT)]@row, 2))) - (VALUE(LEFT([STA (LT)]@row, 2)) * 60 + VALUE(RIGHT([STA (LT)]@row, 2))), IF(AND([Arrival Date]@row < [Actual Arrival Date]@row, OR([ETA/ATA (LT)]@row >= [STA (LT)]@row, [ETA/ATA (LT)]@row < [STA (LT)]@row)), ((24 * 60) - (VALUE(LEFT([STA (LT)]@row, 2)) * 60 + VALUE(RIGHT([STA (LT)]@row, 2)))) + ((VALUE(LEFT([ETA/ATA (LT)]@row, 2)) * 60 + VALUE(RIGHT([ETA/ATA (LT)]@row, 2)))), IF(AND([Arrival Date]@row < [Actual Arrival Date]@row, [ETA/ATA (LT)]@row < [STA (LT)]@row), ((24 * 60) - (VALUE(LEFT([STA (LT)]@row, 2)) * 60 + VALUE(RIGHT([STA (LT)]@row, 2)))) - ((VALUE(LEFT([ETA/ATA (LT)]@row, 2)) * 60 + VALUE(RIGHT([ETA/ATA (LT)]@row, 2)))), ""))))