date/time in a cell is after 5Pm friday and before 3PM Sunday

[derickj]

I need to create a formula in excel to evaluate if the date/time in a cell (opened) is after 5Pm friday and before 3PM Sunday. Can someone help?

Number​	After Friday 5PM and Before Sunday 3PM	Opened​
INC2632724​		2020-08-01 08:55:44​
INC2632725​		2020-08-01 09:37:55​
INC2632762​		2020-08-01 11:56:51​
INC2633319​		2020-08-02 07:45:33​
INC2634522​		2020-08-02 11:24:01​
INC2633588​		2020-08-02 19:37:06​
INC2634252​		2020-08-02 23:26:58​
INC2634365​		2020-08-03 00:10:05​
INC2634427​		2020-08-03 00:31:15​
INC2634430​		2020-08-03 00:32:57​
INC2634456​		2020-08-03 00:42:48​

 

[JamesCanale]

MrExcelPlayground4.xlsx
	A	B	C
31	Number	After Friday 5PM and Before Sunday 3PM	Opened
32	INC2632724	Yes	2020-08-02 14:01:44
33	INC2632725	No	2020-08-02 21:37:55
34	INC2632762	Yes	2020-08-01 11:56:51
Sheet26

Cell Formulas
Range	Formula
B32:B34	B32	=IF(AND(WEEKDAY(C32,15)+MOD(C32,1)>=1.70833,WEEKDAY(C32,15)+MOD(C32,1)<=3.625),"Yes","No")

 

[derickj]

MrExcelPlayground4.xlsx
	A	B	C
31	Number	After Friday 5PM and Before Sunday 3PM	Opened
32	INC2632724	Yes	2020-08-02 14:01:44
33	INC2632725	No	2020-08-02 21:37:55
34	INC2632762	Yes	2020-08-01 11:56:51
Sheet26

Cell Formulas
Range	Formula
B32:B34	B32	=IF(AND(WEEKDAY(C32,15)+MOD(C32,1)>=1.70833,WEEKDAY(C32,15)+MOD(C32,1)<=3.625),"Yes","No")

thanks

 

[derickj]

MrExcelPlayground4.xlsx
	A	B	C
31	Number	After Friday 5PM and Before Sunday 3PM	Opened
32	INC2632724	Yes	2020-08-02 14:01:44
33	INC2632725	No	2020-08-02 21:37:55
34	INC2632762	Yes	2020-08-01 11:56:51
Sheet26

Cell Formulas
Range	Formula
B32:B34	B32	=IF(AND(WEEKDAY(C32,15)+MOD(C32,1)>=1.70833,WEEKDAY(C32,15)+MOD(C32,1)<=3.625),"Yes","No")

I need to stop time to by 1PM instead of 3PM. What does 3.625 need to change to?

 

[JamesCanale]

3+13/24

 

[derickj]

I need to change the time to after 6PM Friday to 1PM Sunday.

 

[derickj]

3+13/24

?

 

[JamesCanale]

You have to look into the "WEEKDAY" function. I use argument '15' to mean that Friday = 1 and Thursday =7 of each week.
So Sunday is 3. Sunday at noon is 3.5. Sunday at 1:00PM is 3+13/24.

I'm mostly rusty on this problem.

 

[derickj]

=IF(AND(WEEKDAY(C5,15)+MOD(C5,1)>=1.75,WEEKDAY(C5,15)+MOD(C5,1)<=3.5417),"Yes","No") I think

 

[derickj]

You have to look into the "WEEKDAY" function. I use argument '15' to mean that Friday = 1 and Thursday =7 of each week.
So Sunday is 3. Sunday at noon is 3.5. Sunday at 1:00PM is 3+13/24.

I'm mostly rusty on this problem.

I think I understand the logic and this revised functions seems to work for the new parameters
=IF(AND(WEEKDAY(C5,15)+MOD(C5,1)>=1.75,WEEKDAY(C5,15)+MOD(C5,1)<=3.5417),"Yes","No")
Thanks for all your help