Calculate Elapsed Time in Minutes (results may be negative, or past midnight)

[rgsanchez]

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.

[TABLE="width: 955"]
<tbody>[TR]
[TD]Scenario[/TD]
[TD]Scenario Description[/TD]
[TD]Scheduled
Train Arrival[/TD]
[TD]Actual
Train Arrival[/TD]
[TD]Current Function Used[/TD]
[TD="align: center"]Result
Minutes Early/Late[/TD]
[TD]Desired Result?[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Train was late[/TD]
[TD]8:58 AM[/TD]
[TD]9:05 AM[/TD]
[TD]=IFERROR((D5-C5)*1440,"")[/TD]
[TD] 7.00[/TD]
[TD] Yes[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Train was early[/TD]
[TD]5:50 PM[/TD]
[TD]5:48 PM[/TD]
[TD]=IFERROR((D6-C6)*1440,"")[/TD]
[TD] (2.00)[/TD]
[TD] Yes[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Train was exactly on-time[/TD]
[TD]7:07 PM[/TD]
[TD]7:07 PM[/TD]
[TD]=IFERROR((D7-C7)*1440,"")[/TD]
[TD] (0.00)[/TD]
[TD] Yes[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Train arrived after midnight.[/TD]
[TD]11:03 PM[/TD]
[TD]12:00 AM[/TD]
[TD]=IFERROR((D8-C8)*1440,"")[/TD]
[TD] (1,383.00)[/TD]
[TD] No (should be "57")[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Train arrived after midnight.[/TD]
[TD]11:03 PM[/TD]
[TD]missing[/TD]
[TD]=IFERROR((D9-C9)*1440,"")[/TD]
[TD][/TD]
[TD] Yes[/TD]
[/TR]
</tbody>[/TABLE]

 

[Toadstool]

As 12:00AM is actually the next day (00:00 in 24 hours clock) then you really need the Scheduled and Arrival dates and the maths will work.

[TABLE="class: grid, width: 476"]
<tbody>[TR]
[TD]

Scheduled Train Arrival
​

​

[/TD]
[TD]

Actual Train Arrival​

​

[/TD]
[TD]

Result​

​

[/TD]
[/TR]
[TR]
[TD="align: right"]

1/1/2019 23:03​

​

[/TD]
[TD="align: right"]

1/2/2019 0:00​

​

[/TD]
[TD="align: right"]

57​

​

[/TD]
[/TR]
</tbody>[/TABLE]

 

[rgsanchez]

As 12:00AM is actually the next day (00:00 in 24 hours clock) then you really need the Scheduled and Arrival dates and the maths will work.

[TABLE="class: grid, width: 476"]
<tbody>[TR]
[TD]

Scheduled Train Arrival
​

[/TD]
[TD]

Actual Train Arrival​

[/TD]
[TD]

Result​

[/TD]
[/TR]
[TR]
[TD="align: right"]

1/1/2019 23:03​

[/TD]
[TD="align: right"]

1/2/2019 0:00​

[/TD]
[TD="align: right"]

57​

[/TD]
[/TR]
</tbody>[/TABLE]

Hi Toadstool,
Thank you for your response, but I am looking for a solution that does not require me to use the full dates, as that would mean I'd have to make changes to my raw data.
I did see a solution in one of the forums that solves the 'after midnight' scenario 4. However, I can't use it because the function doesn't work well for all my other scenarios. I am looking for one function that will work with all five scenarios.

Solution to 'after midnight' scenario without using full dates (posted by someone else from another forum):
B2: 23:00
C2: 1:37
D2: =C2-B2+(B2>C2)


Thank you,
Rosa

 

[Toadstool]

Rosa,

There can't be a 100% solution as without the date it has to be a judgement call.

We see the midnight and judge the train must have been 57 minutes late, but what if it's actual arrival was 6:03 PM, would we say it's 18 hours and 57 minutes late or 5 hours early?

If you can make the judgement that any difference of more than 12 hours (720 minutes) probably means the start and actual times go over midnight.

I've added another scenario with a scheduled after midnight time and actual before midnight and use formula:
=IFERROR(IF((D3-C3)*1440>720,((D3-C3)*1440)-1440,IF((D3-C3)*1440<-720,((D3-C3)*1440)+1440,(D3-C3)*1440)),"")

	A	B	C	D	E	F
Scenario	Scenario Description	Scheduled	Actual	Current Function Used	Result
Train Arrival	Minutes Early/Late
Train was late
Train was early
Train was exactly on-time
Train arrived after midnight.
Train arrived after midnight.	missing
Train scheduled after midnight

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"]8:58 AM[/TD]
[TD="align: right"]9:05 AM[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]7[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]2[/TD]

[TD="align: right"]5:50 PM[/TD]
[TD="align: right"]5:48 PM[/TD]
[TD="align: right"]-2[/TD]
[TD="align: right"]-2[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]3[/TD]

[TD="align: right"]7:07 PM[/TD]
[TD="align: right"]7:07 PM[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]4[/TD]

[TD="align: right"]11:03 PM[/TD]
[TD="align: right"]12:00 AM[/TD]
[TD="align: right"]-1383[/TD]
[TD="align: right"]57[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]5[/TD]

[TD="align: right"]11:03 PM[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]6[/TD]

[TD="align: right"]12:05 AM[/TD]
[TD="align: right"]11:45 PM[/TD]
[TD="align: right"]1420[/TD]
[TD="align: right"]-20[/TD]

</tbody>

Sheet1 (2)​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E3[/TH]
[TD="align: left"]=IFERROR((D3-C3)*1440,"")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F3
[/TH]
[TD="align: left"]=IFERROR(IF((D3-C3)*1440>720,((D3-C3)*1440)-1440,IF((D3-C3)*1440<-720,((D3-C3)*1440)+1440,(D3-C3)*1440)),"")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[rgsanchez]

Rosa,

There can't be a 100% solution as without the date it has to be a judgement call.

We see the midnight and judge the train must have been 57 minutes late, but what if it's actual arrival was 6:03 PM, would we say it's 18 hours and 57 minutes late or 5 hours early?

If you can make the judgement that any difference of more than 12 hours (720 minutes) probably means the start and actual times go over midnight.

I've added another scenario with a scheduled after midnight time and actual before midnight and use formula:
=IFERROR(IF((D3-C3)*1440>720,((D3-C3)*1440)-1440,IF((D3-C3)*1440<-720,((D3-C3)*1440)+1440,(D3-C3)*1440)),"")

	A	B	C	D	E	F
Scenario	Scenario Description	Scheduled	Actual	Current Function Used	Result
Train Arrival	Minutes Early/Late
Train was late
Train was early
Train was exactly on-time
Train arrived after midnight.
Train arrived after midnight.	missing
Train scheduled after midnight

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"]8:58 AM[/TD]
[TD="align: right"]9:05 AM[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]7[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]2[/TD]

[TD="align: right"]5:50 PM[/TD]
[TD="align: right"]5:48 PM[/TD]
[TD="align: right"]-2[/TD]
[TD="align: right"]-2[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]3[/TD]

[TD="align: right"]7:07 PM[/TD]
[TD="align: right"]7:07 PM[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]4[/TD]

[TD="align: right"]11:03 PM[/TD]
[TD="align: right"]12:00 AM[/TD]
[TD="align: right"]-1383[/TD]
[TD="align: right"]57[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]5[/TD]

[TD="align: right"]11:03 PM[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]6[/TD]

[TD="align: right"]12:05 AM[/TD]
[TD="align: right"]11:45 PM[/TD]
[TD="align: right"]1420[/TD]
[TD="align: right"]-20[/TD]

</tbody>

Sheet1 (2)​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]E3[/TH]
[TD="align: left"]=IFERROR((D3-C3)*1440,"")[/TD]
[/TR]
[TR]
[TH]F3[/TH]
[TD="align: left"]=IFERROR(IF((D3-C3)*1440>720,((D3-C3)*1440)-1440,IF((D3-C3)*1440<-720,((D3-C3)*1440)+1440,(D3-C3)*1440)),"")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Hi Toadstool,
Your function in F3 solved my issue! Typically a train will never be later, and definitely not earlier, than even 5 hours. Trains that are more than 3 hours late typically just get cancelled. Thank you so much for your help. I am working with a very large data set that needs regular updating. Not having to use the complete date will save me a lot of time. Thanks again!

 

[Toadstool]

You're welcome!