Struggling with calculating early or late flights (time differences)

[pete_agreatguy]

Hi all,

I am struggling with x2 formulas in regards to calculating if a Flight is either: Early (if so, how early in mins : secs), On Time (needs to be included in the early column) or Late (if so, how late in mins : secs):



IMPORTANT NOTES:

1) Columns M and P are formatted as custom "hh:mm:ss" ... and ... columns S and T are formatted as custom "mm:ss"

2) I need an "on time" flight to be included in the "early" column

3) I need the main formulas to be inside IF(ISERROR([formula])),"",[formula]) as I need blank records if data cannot be found elsewhere (to avoid N/A results)

3) A flight is considered on time / early between e.g. 12:54:00 - 12:54:59 if due at 12:54:00

4) A flight is considered late if it is after e.g. 12:54:59 (therefore 12:55:00+) if due at 12:54:00

E.g. flight AIJ6979 (highlighted) was due to takeoff at 12:54:00. He took off at 12:54:59 so he is on time yet my formulas are calculating that this flight is late by 00:00.

Current formulas:

Columns J, M and P have a INDEX MATCH formula as they are looked up from an import sheet.

Column S: =IF(ISERROR($M38-$P38+TIME(0,0,59)),"",$M38-$P38+TIME(0,0,59))

Column T: =IF(ISERROR($P38-$M38-TIME(0,0,59)),"",$P38-$M38-TIME(0,0,59))

Please help me fix this because I am unsure why this has provided such results

 

[GraH - Guido]

Hi Pete, to be a great guy overhere, please have a read of the forum rules and when posting please provide a sample file. Help us help you.
... and welcome to the wonder works of Chandoo's finest.

 

[pete_agreatguy]

As per request I've attached a temporary workbook to help resolve my time calculation issues.

In the attached I have renamed columns "D" (Due to takeoff @) and "G" (Actual takeoff time) for easier understanding.

As you can see I thought that ...

1) To find out if a flight had taken off on time or early, it was a simple case of subtracting the "actual time" from the "due time" - adding 59 seconds for leeway using the TIME function. As you can see for the highlighted flight AIJ6979, my formulas are calculating that this flight is late, even though this flight is on time (just - i.e. by 0 second).

2) To find out if a flight was late taking off, it was a simple case of subtracting the "due time" from the "actual time". For example if we change flight AIJ6979 to have an "Actual TO time" of 12:55:00, the late and early formulas work fine and as should.

What I would like the data to show:

2) I need an "on time" flight to be included in the "early" column

3) I need the main formulas to be inside IF(ISERROR([formula])),"",[formula]) as I need blank records if data cannot be found elsewhere (to avoid #N/A results)

3) A flight is considered on time / early between e.g. 12:54:00 - 12:54:59 if due at 12:54:00

4) A flight is considered late if it is after e.g. 12:54:59 (therefore 12:55:00+) if due at 12:54:00

E.g. flight AIJ6979 (highlighted) was due to takeoff at 12:54:00. He took off at 12:54:59 so he is on time yet my formulas are calculating that this flight is late by 00:00.

 

[pete_agreatguy]

Should I be using an IF statement? If so, I have no idea where to start as my mental health isn't too great at the moment so am unable to concentrate

 

[bosco_yip]

Maybe,

Changed you Column J and Column K Custom Format Cells

From this :

mm:ss

To this (add 3 ";" behind ss) :

mm:ss;;;

Regards
Bosco

 

[pete_agreatguy]

Many thanks for your response @bosco_yip , this has helped remove negative results.

However the issue of a flight appearing late when it is actually on time, still exists.

Re-attached the change implemented: