Need formula for Elapsed Time (negative values, times after midnight)

[rgsanchez]

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).

[TABLE="width: 564"]
[TR]
[TD]Scenario[/TD]
[TD]Scenario Description[/TD]
[TD]Minutes Early/Late[/TD]
[TD]Scheduled
Train Arrival[/TD]
[TD]Actual
Train Arrival[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Train was late[/TD]
[TD] 7.00[/TD]
[TD]8:58 AM[/TD]
[TD]9:05 AM[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Train was early[/TD]
[TD] (2.00)[/TD]
[TD]5:50 PM[/TD]
[TD]5:48 PM[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Train was exactly on-time[/TD]
[TD] (0.00)[/TD]
[TD]7:07 PM[/TD]
[TD]7:07 PM[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Train arrived after midnight.[/TD]
[TD] (1,383.00)[/TD]
[TD]11:03 PM[/TD]
[TD]12:00 AM[/TD]
[/TR]
[/TABLE]
Scenario 4 should result in 57 minutes late, not 1,383 minutes early.

 

[Toadstool]

You seem to have duplicated the question. Pease see my response to your later submission here: https://www.mrexcel.com/forum/excel...ve-past-midnight-post5334408.html#post5334408

 

[rgsanchez]

Thanks, Toadstool. I am new to Mr. Excel and I accidentally posted in the wrong forum at first. I will delete this post above.

 

[rgsanchez]

Actually, I don't know how to delete it! Sorry for the double post.