Formula for Date\Time

[Freeman2022]

Hello,

Can someone please help me with the below, I'm looking for a formula that would give me the information below. Thanks in advance

If column A is Monday - Thursday and time is 9PM to 6AM then Column B = Afterhours, if not then Column B = Business Hours If column A is Friday and time is 5PM to 6AM then Column B = Afterhours, if not then Column B = Business Hours If column A is Sat or Sun then Column B = Afterhours

 

[JamesCanale]

MrExcelPlayground16.xlsx
	A	B
1	Date Time
2	3/10/2023 9:40	Business Hours
3	3/10/2023 14:39	Business Hours
4	3/10/2023 21:41	Afterhours
5	3/11/2023 7:36	Afterhours
6	3/11/2023 16:55	Afterhours
7	3/12/2023 3:31	Afterhours
8	3/12/2023 6:02	Afterhours
9	3/12/2023 16:21	Afterhours
10	3/12/2023 17:49	Afterhours
11	3/13/2023 2:18	Afterhours
12	3/13/2023 9:05	Business Hours
13	3/13/2023 20:31	Business Hours
14	3/14/2023 6:08	Business Hours
15	3/14/2023 17:04	Business Hours
16	3/14/2023 23:37	Afterhours
17	3/15/2023 7:30	Business Hours
18	3/15/2023 9:50	Business Hours
19	3/15/2023 17:37	Business Hours
20	3/15/2023 18:45	Business Hours
21	3/15/2023 22:17	Afterhours
22	3/15/2023 23:51	Afterhours
23	3/16/2023 4:10	Afterhours
24	3/16/2023 11:36	Business Hours
25	3/16/2023 19:14	Business Hours
26	3/17/2023 0:10	Afterhours
27	3/17/2023 9:15	Business Hours
28	3/17/2023 16:00	Business Hours
Sheet22

Cell Formulas
Range	Formula
B2:B28	B2	=IF(WEEKDAY(A2,11)<5,IF(AND(HOUR(A2)>=6,HOUR(A2)<21),"Business Hours","Afterhours"),IF(WEEKDAY(A2,11)=5,IF(AND(HOUR(A2)>=6,HOUR(A2)<17),"Business Hours","Afterhours"),"Afterhours"))

 

[Freeman2022]

MrExcelPlayground16.xlsx
	A	B
1	Date Time
2	3/10/2023 9:40	Business Hours
3	3/10/2023 14:39	Business Hours
4	3/10/2023 21:41	Afterhours
5	3/11/2023 7:36	Afterhours
6	3/11/2023 16:55	Afterhours
7	3/12/2023 3:31	Afterhours
8	3/12/2023 6:02	Afterhours
9	3/12/2023 16:21	Afterhours
10	3/12/2023 17:49	Afterhours
11	3/13/2023 2:18	Afterhours
12	3/13/2023 9:05	Business Hours
13	3/13/2023 20:31	Business Hours
14	3/14/2023 6:08	Business Hours
15	3/14/2023 17:04	Business Hours
16	3/14/2023 23:37	Afterhours
17	3/15/2023 7:30	Business Hours
18	3/15/2023 9:50	Business Hours
19	3/15/2023 17:37	Business Hours
20	3/15/2023 18:45	Business Hours
21	3/15/2023 22:17	Afterhours
22	3/15/2023 23:51	Afterhours
23	3/16/2023 4:10	Afterhours
24	3/16/2023 11:36	Business Hours
25	3/16/2023 19:14	Business Hours
26	3/17/2023 0:10	Afterhours
27	3/17/2023 9:15	Business Hours
28	3/17/2023 16:00	Business Hours
Sheet22

Cell Formulas
Range	Formula
B2:B28	B2	=IF(WEEKDAY(A2,11)<5,IF(AND(HOUR(A2)>=6,HOUR(A2)<21),"Business Hours","Afterhours"),IF(WEEKDAY(A2,11)=5,IF(AND(HOUR(A2)>=6,HOUR(A2)<17),"Business Hours","Afterhours"),"Afterhours"))

Works PERFECTLY!!!!!! Thank you so much

 

[Jeffrey Mahoney]

This is the based on about 15 minutes of work. Maybe there is somthing simpler
=LET(WD,WEEKDAY(A2,2),T,TIME(HOUR(A2),MINUTE(A2),SECOND(A2)),IF(OR(OR(AND(WD>=1,WD<=4,OR(T<TIMEVALUE("06:00:00"),T>TIMEVALUE("21:00:00"))),AND(WD=5,OR(T<TIMEVALUE("06:00"),T>TIMEVALUE("17:00"))),WD>5)),"Afterhours","Business Hours"))