Departure delay

[jagm68]

Hello,

First time I use this page, I would like to know if you can help me with the following issue.

I need to calculate the minutes delay for a departure time, the formula I use is.

let say departure time is 10:00 in cell A1, and schedule time 10:00 is in cell B1, I use =if(A1>B1,A1-B1,0) that gives me the result, if departure time is after 10:00 I will get the minutes delay, however if departure time is after midnight I get as result 0, I would like to have the total time from departure including the time after midnight, I hope That could explain mysefl well, thanks in advance for your help.

 

[ironsides]

use a 24 hr clock

 

[jagm68]

Does not work

 

[T. Valko]

Does not work

If these were your times what result do you expect:

Departure time = 11:50 PM
Schedule time = 11:55 PM

 

[jagm68]

No,

all times are in 24time, this is the problem, let say departure time is 23:00, any time before that will reflect 0 minutes delay as departure is on time, if departure is after 23:00 will reflect the minutes delay, 23:10 will reflect 10 minutes delay, but if departure is at 00:05 I would like to reflect 65 minutes delay, but is showing 0 minutes delay because is not adding the 5 minutes for the next day

 

[ironsides]

No,

all times are in 24time, this is the problem, let say departure time is 23:00, any time before that will reflect 0 minutes delay as departure is on time, if departure is after 23:00 will reflect the minutes delay, 23:10 will reflect 10 minutes delay, but if departure is at 00:05 I would like to reflect 65 minutes delay, but is showing 0 minutes delay because is not adding the 5 minutes for the next day

midnite 2400

1AM 2500

 

[AhoyNC]

This formula should work for you.
A B C
<TABLE style="WIDTH: 187pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=249><COLGROUP><COL style="WIDTH: 58pt; mso-width-source: userset; mso-width-alt: 2816" width=77><COL style="WIDTH: 65pt; mso-width-source: userset; mso-width-alt: 3181" width=87><COL style="WIDTH: 64pt; mso-width-source: userset; mso-width-alt: 3108" width=85><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #b8cce4; WIDTH: 58pt; HEIGHT: 15pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=20 width=77>Sch. Time</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #b8cce4; WIDTH: 65pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=87>Dept. Time</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #b8cce4; WIDTH: 64pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl66 width=85>Delay</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 height=20 align=right>11:00 PM</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>11:30 PM</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl68 align=right>0:30</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69 height=21 align=right>11:00 PM</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70 align=right>12:05 AM</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl71 align=right>1:05</TD></TR></TBODY></TABLE>

<TABLE style="WIDTH: 100pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=133><COLGROUP><COL style="WIDTH: 100pt; mso-width-source: userset; mso-width-alt: 4864" width=133><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 100pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 height=20 width=133>=MOD(B2-A2,1)</TD></TR></TBODY></TABLE>

=MOD(Dept. Time - Sch. time,1)

This video will explain why this works.

http://www.youtube.com/user/excelisfun#p/search/0/btiuexHSvHY

 

[jagm68]

It works, but now the problem is when dept. time it is a few minutes early than schedule is showing all the minutes like the it is the next day, if the departure is before schedule I would like to show 0

 

[AhoyNC]

I see what you mean, but I'm not sure of an answer.

 

[jagm68]

Thanks, I will keep trying