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):
excel_timehelp2.JPG
What I would like the data to show (inc. important notes):
1) Columns D and G are formatted as custom "hh:mm:ss" ... and ... columns J and K 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:
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" - subtracting 59 seconds for leeway using the TIME function. 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.
Column J: =IF(ISERROR($M38-$P38+TIME(0,0,59)),"",$M38-$P38+TIME(0,0,59))
Column K: =IF(ISERROR($P38-$M38-TIME(0,0,59)),"",$P38-$M38-TIME(0,0,59))
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
Please help me fix this because I am unsure why this has provided such results
Bookmarks