If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Establishing a Time Window
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. |
#2
|
|||
|
|||
Establishing a Time Window
Try
=IF($Z2=0,"ATTENTION",IF($Z2$AB2,"EARLY",IF($Z2$ AD2,"ON TIME","LATE"))) -- HTH Bob "NoodNutt" wrote in message ... 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. |
#3
|
|||
|
|||
Establishing a Time Window
Whoa! works a treat.
Thx heaps Bob Your a champ. Cheers Mark. |
Thread Tools | |
Display Modes | |
|
|