Working with Early and late arrivals

[thesilkster]

Hello I have a formula
=IF(OR(J4="",I4=J4),"",IF(0>((MOD(J4-I4,1)>0.5)-MOD(J4-I4,1)),"LATE","EARLY"))

if the vehicle arrives on time ie 23:30 the cell shows blank instead of Early

how can I stop this happening please its driving me mad

 

[Marc L]

Hi !

in this case 23:29 is EARLY as 23:30 can't be, just ON TIME !​

 

[thesilkster]

Hi 23:30 needs to populate "Early" although it is in fact On Time

thanks

 

[GraH - Guido]

It is in your formula: IF(OR(J4="",I4=J4),""
Per your expectations that part is wrong, because when equal you consider it early.
this seems to work as you want it to do
=IF(J4="","",IF(0>((MOD(J4-I4,1)>=0,5)-MOD(J4-I4,1)),"LATE","EARLY"))

 

[GraH - Guido]

With only a screenshot at hand, I can't help but think this simplified formula could also work:
=if(J4="","",IF(J4>I4,"Late","early"))

 

[thesilkster]

I did have it simplified but when a truck arrives past midnight it shows early ie 23:55 expected time but arrives at 00:15 shows early

thanks

 

[thesilkster]

It is in your formula: IF(OR(J4="",I4=J4),""
Per your expectations that part is wrong, because when equal you consider it early.
this seems to work as you want it to do
=IF(J4="","",IF(0>((MOD(J4-I4,1)>=0,5)-MOD(J4-I4,1)),"LATE","EARLY"))

I tried this and it will not work sorry getting this

 

[GraH - Guido]

strange, working on my side. See attached file.

 

[thesilkster]

works perfect thanks