Formula for minutes early and late before and after midnight.

[Jingles_88]

Hi,


I have been working on a spreadsheet which displays the minutes a vehicle if early or late on site. My problem however is that some of the arrivals happen after midnight (00:00); and the formulas i have been using work for wither before or after, not both.


[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD]Scheduled On Site[/TD]
[TD]On Site[/TD]
[TD]On Site Variance[/TD]
[/TR]
[TR]
[TD]23:37[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]23:30[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]01:20[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]02:05[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]





I have used the below formula that i found in another forum:
=IF(B2<A2,(B2+1)-(A2),(B2-A2))


The only issue is when the vehicle is early and before midnight the time is incorrect. If i remove the +1 in (B2+1), that issue is solved, but it no longer calculates passed midnight times.


I was wondering if there's a formula that is able to do both. Also, is there a way in which a value doesn't appear in the "On Site Variance" column until a time is entered into the corresponding cell in the "On Site" column.


Any help is greatly welcomed, i have stuck on this for some time.


Many Thanks,


John.

 

[zrx1200]

seems formula cut off?

 

[Jingles_88]

Sorry, i thought i had an attached file. The formula i am using is =IF(B23 < A23,(B23+1)-(A23),(B23-A23))

 

[MarcelBeug]

So I understand that 15 minutes early or late should return 0:15 in either case.

In that case, try

=IF(B23="","",ABS((MOD(B23-A23,1)>0.5)-MOD(B23-A23,1)))

 

[Jingles_88]

Hi Marcel,

That has worked perfectly! .

However it appears that my conditional formatting doesn't work now though. I don't suppose you could assist with that could you? I need it to appear red if late and black if early. If we can go one further, i then need a formula that will return a late or early result based on the time.

I know this seems like a tall order, but every bit of help is greatly appreciated.

Thanks again,

John.

 

[MarcelBeug]

Conditional format formula returns TRUE for late:

=AND(B23<>"",0>((MOD(B23-A23,1)>0.5)-MOD(B23-A23,1)))

 

[Jingles_88]

[SOLVED] Formula for minutes early and late before and after midnight.

For the time being all my issues have been solved.

A Million Thank You's to Marcel for helping me.

Thanks,

John.

 

[MarcelBeug]

Re: [SOLVED] Formula for minutes early and late before and after midnight.

You're welcome.

 

[Jingles_88]

Re: [SOLVED] Formula for minutes early and late before and after midnight.

Hi Marcel,

Another quick question.

That formula for TRUE and FALSE, is there anyway i can change the text to read LATE and EARLY?

John.

 

[MarcelBeug]

Re: [SOLVED] Formula for minutes early and late before and after midnight.

That would be (with a blank for exactly on time):

=IF(OR(B23="",A23=B23),"",IF(0>((MOD(B23-A23,1)>0.5)-MOD(B23-A23,1)),"LATE","EARLY")