Establishing a Time Window

[NoodNutt]

Hi Everyone

I am trying to create a formula that will provide one of the following:

Arrival Window: 40 Mins = 20 Mins Early to 20 Mins Late.

So this is what I have tried, but it does not equate to "On TIme"

'=IF($Z2=0,"ATTENTION",IF($Z2<$AB2,"EARLY",IF($Z2>$AB2<$AD2,"ON
TIME","LATE")))

Row 1 is reserved for column haedings and some fixed references.

Column Y = "21 Mins" Window
Column Z = Actual Arrival Time.
Column AC = Booked Arrival Time
Column AB = 21 Ealry window equation of AC-$Y$1
Column AD = 21 Late window equation of AC+$Y$1

So, with the above equation, it is meant to read like this:

If Z2 = 0 then Attention is required, but if it doesn't, then it needs to
check if Z fits in between the "Greater Than" AB, but "Less Than" AD, which
should display "On Time", else it is either "Early" or "Late".

So far I can get it to display all but "On Time".

A Huge TIA

Regards
Mark.

 

[Bob Phillips]

Try

=IF($Z2=0,"ATTENTION",IF($Z2<$AB2,"EARLY",IF($Z2<$AD2,"ON TIME","LATE")))

 

[NoodNutt]

Whoa! works a treat.

Thx heaps Bob

Your a champ.

Cheers
Mark.