IF Statement with TRUE / FALSE Condition

[gregory77]

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.

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

-G

Table:

D E F
1 Time Weekday? Category

2 06:00 Y

3 06:15 N

4 11:30 Y

5 15:45 Y

6 16:30 Y

7 19:00 N

8 07:15 N

9 17:30 N

10 09:30 Y

11 18:30 N

 

[Scott T]

Try

=IF(AND(E2>=TIMEVALUE("6:00 am"),E2<=TIMEVALUE("10:00 am"),F2="y"),1,IF(AND(E2>=TIMEVALUE("10:01 am"),E2<=TIMEVALUE("4:00 pm"),F2="y"),2,IF(AND(E2>=TIMEVALUE("4:01 pm"),E2<=TIMEVALUE("8:00 pm"),F2="y"),3,IF(F2="n",4))))

 

[gregory77]

Scott T....thank you for the quick reply! Ok, looks like everything is working. However, when Time = 20:00 and Weekday = Y, it evaluates to FALSE. I tried tweaking to no avail. Any suggestions? Again, much appreciated!!!!

 

[Scott T]

It works for me

[TABLE="class: grid, width: 192"]
<tbody>[TR]
[TD="width: 64, bgcolor: transparent, align: right"][/TD]
[TD="width: 64, bgcolor: transparent, align: right"]D
[/TD]
[TD="width: 64, bgcolor: transparent"]E
[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]F
[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent, align: right"]2
[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]20:00
[/TD]
[TD="width: 64, bgcolor: transparent"]y
[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]3
[/TD]
[/TR]
</tbody>[/TABLE]

If you change the format of the cell with 20:00 to General do you get 0.833333?
Check that the y does not have any spaces around. The only thing in the cells should be y.

 

[gregory77]

Strange....If I retype Column D (Time), it works. Column D was originally a calculated field. All the other times work. Weird but probably formatting since I retrieved this from a database. I appreciate it Scott T. Many thanks.

G