IF statement with time format

[kelvin_9]

greeting to all

wondering how can i return A if start time and end time is 8:45-18:15 or B if start time and end time is 9:15-18:45
ps: the report genereated with time format: [$-en-US,1]h:mm
thank you very much for your guidence

Copy of HKDL Schedule List_Marty_Jul 28 - Aug 10.xls
	I	J	K
9	8:45	18:15	A
10	8:45	18:15	A
11	8:45	18:15	A
12	8:45	18:15	A
13	8:45	18:15	A
14	8:45	18:15	A
15	8:45	18:15	A
16	8:45	18:15	A
17	8:45	18:15	A
18	9:15	18:45	B
19	9:15	18:45	B
20	9:15	18:45	B
21	9:15	18:45	B
22	9:15	18:45	B
23	9:15	18:45	B
Sheet1

 

[SunnyAlv]

try

=IF(AND(TEXT(A1,"HH:MM")=TEXT("08:45","HH:MM"),TEXT(B1,"HH:MM")=TEXT("18:15","HH:MM")),"A",IF(AND(TEXT(A1,"HH:MM")=TEXT("09:15","HH:MM"),TEXT(B1,"HH:MM")=TEXT("18:45","HH:MM")),"B",""))

 

[kelvin_9]

thank you for your reply, SunnyAlv

amazing and i tried 2 more criteria, it works too

thank you very much

Copy of HKDL Schedule List_Marty_Jul 28 - Aug 10.xls
	J	K	L
11	8:45	18:15	A
12	8:45	18:15	A
13	8:45	18:15	A
14	8:45	18:15	A
15	8:45	18:15	A
16	8:45	18:15	A
17	8:45	18:15	A
18	8:45	18:15	A
19	8:45	18:15	A
20	9:15	18:45	B
21	9:15	18:45	B
22	9:15	18:45	B
23	9:15	18:45	B
24	9:15	18:45	B
25	9:15	18:45	B
26	10:15	17:45	C
27	12:00	21:30	D
28	12:00	21:30	D
29	12:00	21:30	D
30	12:00	21:30	D
31	12:00	21:00	undefined
Sheet1

Cell Formulas
Range	Formula
L11:L31	L11	=IF(AND(TEXT(J11,"HH:MM")=TEXT("08:45","HH:MM"),TEXT(K11,"HH:MM")=TEXT("18:15","HH:MM")),"A",IF(AND(TEXT(J11,"HH:MM")=TEXT("09:15","HH:MM"),TEXT(K11,"HH:MM")=TEXT("18:45","HH:MM")),"B",IF(AND(TEXT(J11,"HH:MM")=TEXT("10:15","HH:MM"),TEXT(K11,"HH:MM")=TEXT("17:45","HH:MM")),"C",IF(AND(TEXT(J11,"HH:MM")=TEXT("12:00","HH:MM"),TEXT(K11,"HH:MM")=TEXT("21:30","HH:MM")),"D","undefined"))))

 

[felixstraube]

When in the first column you have 8:45, the second columna always has 18:15? And for the case that in the first column is 9:15, the second is always 18:45? Or could there be any combination of time values in both columns?

 

[felixstraube]

Another option:

Book1
	A	B	C
1	8:45	18:15	A
2	8:45	18:15	A
3	8:45	18:15	A
4	8:45	18:15	A
5	8:45	18:15	A
6	8:45	18:15	A
7	8:45	18:15	A
8	8:45	18:15	A
9	8:45	18:15	A
10	9:15	18:45	B
11	9:15	18:45	B
12	9:15	18:45	B
13	9:15	18:45	B
14	9:15	18:45	B
15	9:15	18:45	B
16	10:15	17:45	C
17	12:00	21:30	D
18	12:00	21:30	D
19	12:00	21:30	D
20	12:00	21:30	D
21	12:00	21:00	not defined
Sheet1

Cell Formulas
Range	Formula
C1:C21	C1	=IFERROR(INDEX({"A";"B";"C";"D"}, MATCH(TEXT(A1, "h:mm")&"-"&TEXT(B1, "h:mm"),{"8:45-18:15";"9:15-18:45";"10:15-17:45";"12:00-21:30"},0)), "not defined")

 

[kelvin_9]

thank you for your reply, felixstraube

impeccable

thank you very much

 

[felixstraube]

Thanks for the feedback. Happy to help.

 

[Scott Huish]

If you won't have more than 26 possibilities, this should work too:

=IFERROR(CHAR(64+MATCH(TEXT(A1,"h:mm")&"-"&TEXT(B1,"h:mm"),{"8:45-18:15";"9:15-18:45";"10:15-17:45";"12:00-21:30"},0)),"not defined")

Book3
	A	B	C
1	8:45	18:15	A
2	8:45	18:15	A
3	8:45	18:15	A
4	8:45	18:15	A
5	8:45	18:15	A
6	8:45	18:15	A
7	8:45	18:15	A
8	8:45	18:15	A
9	8:45	18:15	A
10	9:15	18:45	B
11	9:15	18:45	B
12	9:15	18:45	B
13	9:15	18:45	B
14	9:15	18:45	B
15	9:15	18:45	B
16	10:15	17:45	C
17	12:00	21:30	D
18	12:00	21:30	D
19	12:00	21:30	D
20	12:00	21:30	D
21	12:00	21:00	not defined
Sheet1

Cell Formulas
Range	Formula
C1:C21	C1	=IFERROR(CHAR(64+MATCH(TEXT(A1,"h:mm")&"-"&TEXT(B1,"h:mm"),{"8:45-18:15";"9:15-18:45";"10:15-17:45";"12:00-21:30"},0)),"not defined")