If statement help

[Justin Greenwood]

Hi guys so here is the issue.

In column B I have a bunch of arrival time ranging from 06:00:00 to 23:59:00 (B is formatted to hh:mm)
In column A I want it to say M or A or L
06:00 - 14:00 = M
14:01 - 22:00 = A
22:01 - 23:59 = L

I just cant get the IF statement to work

=IF(B1<="14:00","M",IF(B1=">=22:00:00","L","A"))

 

[NARAYANK991]

Hi ,

Try this :

=IF(B1<="14:00","M",IF(B1>="22:01:00","L","A"))

Narayan

 

[Justin Greenwood]

It is only showing M's
I have attached a sample.

 

[bosco_yip]

A2 copy down :

=IF(B2="","",LOOKUP(B2,{0.25;0.584;0.917},{"M";"A";"L"}))

Regards

 

[NARAYANK991]

Hi ,

Change the formula to :

=IF(B2<=TIMEVALUE("14:00"),"M",IF(B2>=TIMEVALUE("22:01:00"),"L","A"))

Narayan

 

[NARAYANK991]

Hi ,

You can also use :

=IF(B2 = "", "", IF(B2<=0 + ("14:00"),"M",IF(B2>=0 +("22:01:00"),"L","A")))

Narayan

 

[Justin Greenwood]

Thank you Bosco not sure how it works but it works so that is fine with me

 

[Chihiro]

Time portion in Narayan's formula can also be replaced with Time function.

=IF(B2="","",IF(B2<=TIME(14,0,0),"M",IF(B2>TIME(22,0,0),"L","A")))

 

[Justin Greenwood]

Thanks for all the help guys and gals managed to get it to work so!