Hello,
I need to calculate how many minutes early/late a train arrived. I am comparing the scheduled train arrival, versus the actual train arrival. I have tried many formulas, but can't find a formula that works for all five scenarios I've encountered in my data below. Please note I must use the 1900 Excel date system (Windows default) and the calculated minutes early/late must remain numeric (not text), so that I can use those values in future calculations.
<tbody>
</tbody>
I need to calculate how many minutes early/late a train arrived. I am comparing the scheduled train arrival, versus the actual train arrival. I have tried many formulas, but can't find a formula that works for all five scenarios I've encountered in my data below. Please note I must use the 1900 Excel date system (Windows default) and the calculated minutes early/late must remain numeric (not text), so that I can use those values in future calculations.
Scenario | Scenario Description | Scheduled Train Arrival | Actual Train Arrival | Current Function Used | Result Minutes Early/Late | Desired Result? |
1 | Train was late | 8:58 AM | 9:05 AM | =IFERROR((D5-C5)*1440,"") | 7.00 | Yes |
2 | Train was early | 5:50 PM | 5:48 PM | =IFERROR((D6-C6)*1440,"") | (2.00) | Yes |
3 | Train was exactly on-time | 7:07 PM | 7:07 PM | =IFERROR((D7-C7)*1440,"") | (0.00) | Yes |
4 | Train arrived after midnight. | 11:03 PM | 12:00 AM | =IFERROR((D8-C8)*1440,"") | (1,383.00) | No (should be "57") |
5 | Train arrived after midnight. | 11:03 PM | missing | =IFERROR((D9-C9)*1440,"") | Yes |
<tbody>
</tbody>