IF statement with a TRUE / FALSE condition

[gregorii77]

Given the table, I have the following criteria (times in 24 hour):

IF 6:00 to 10:00 AND weekday = Y THEN Category = 1
IF 10:01 to 16:00 AND weekday = Y THEN Category = 2
IF 16:01 to 20:00 AND weekday = Y THEN Category = 3

IF 06:00 to 20:00 AND weekday = N THEN Category = 4

There are no times before 06:00 or after 20:00. I am trying to contrive the formula for use in F2. Uploaded file included.

My efforts have been met with disastrous results. Any assistance you could provide would be fantastic and greatly appreciated! Thank you so much!

 

[Chihiro]

Try...
=IF((MEDIAN(TIME(6,0,0),TIME(10,0,0),D2)=D2)*(E2="Y"),1,IF((MEDIAN(TIME(10,0,1),TIME(16,0,0),D2)=D2)*(E2="Y"),2,IF(E2="N",4,3)))

 

[gregorii77]

Worked like a charm! For my own personal edification, how is it using the MEDIAN function? I know what the median is but could you explain it's purpose here? Much appreciated!!!!!!!!!!!!!! G

 

[Chihiro]

Basically Median is used to check that values in D2 falls between 2 times.
Where TIME(hour,minutes,seconds).

If Median return D2 value, then it is between those 2 times.

You only need to check for first 2 time ranges, then check for E2="N". If all of those conditions are not met, then value must default to Category 3.

 

[bosco_yip]

Another option.

=IF(E2="y",MATCH(D2,0+{"6:00","10:01","16:01"}),4)

Regards
Bosco