Hello,
I need to calculate how minutes late/early a train arrived (elapsed time) by comparing the scheduled arrival time with the actual arrival time. I have tried many different ways of doing this, but none of the ways seem to work for all four scenarios I've encountered with the data. Please note that I must keep the 1990 date system (Windows default). Also, I need to keep the results in numeric values that I can use to do future calculations with. The same formula must work for all four scenarios. Please see below (I don't know how to attach the actual Excel sheet).
Scenario 4 should result in 57 minutes late, not 1,383 minutes early.
I need to calculate how minutes late/early a train arrived (elapsed time) by comparing the scheduled arrival time with the actual arrival time. I have tried many different ways of doing this, but none of the ways seem to work for all four scenarios I've encountered with the data. Please note that I must keep the 1990 date system (Windows default). Also, I need to keep the results in numeric values that I can use to do future calculations with. The same formula must work for all four scenarios. Please see below (I don't know how to attach the actual Excel sheet).
Scenario | Scenario Description | Minutes Early/Late | Scheduled Train Arrival | Actual Train Arrival |
1 | Train was late | 7.00 | 8:58 AM | 9:05 AM |
2 | Train was early | (2.00) | 5:50 PM | 5:48 PM |
3 | Train was exactly on-time | (0.00) | 7:07 PM | 7:07 PM |
4 | Train arrived after midnight. | (1,383.00) | 11:03 PM | 12:00 AM |