Formula to determine if time is within range

[odonovanc]

Let's say I have a cell that has the following value in cell A1: 8/31/2023 11:54:00 PM. The cell is in mm/dd/yyyy hh:mm format.

I want to write a formula that will tell me if the time in that cell is between 23:00 (11:00 PM) and 7:00 (7:00 AM). I need the date to be ignored altogether.

Help!!

 

[Fluff]

How about

=IF(MEDIAN(INT(A1)+TIME(11,0,0),INT(A1)+31/24,A1)=A1,"y","n")

 

[odonovanc]

How about

=IF(MEDIAN(INT(A1)+TIME(11,0,0),INT(A1)+31/24,A1)=A1,"y","n")

Not working. For example, for this time 8/31/2023 10:05:00 PM it gave me a y it should have been a n. also 8/25/2023 4:01:00 AM gave me a n but should be a y

 

[Fluff]

Not for me it doesn't

Fluff.xlsm
	A	B
1	31/08/2023 10:05	n
2	31/08/2023 11:54	y
Sheet6

Cell Formulas
Range	Formula
B1:B2	B1	=IF(MEDIAN(INT(A1)+TIME(11,0,0),INT(A1)+31/24,A1)=A1,"y","n")

 

[Fluff]

Oops won't work for the morning, try

=IF(OR(MOD(A1,1)>=11/24,MOD(A1,1)<=7/24),"y","n")

 

[odonovanc]

Not for me it doesn't

Fluff.xlsm
	A	B
1	31/08/2023 10:05	n
2	31/08/2023 11:54	y
Sheet6

Cell Formulas
Range	Formula
B1:B2	B1	=IF(MEDIAN(INT(A1)+TIME(11,0,0),INT(A1)+31/24,A1)=A1,"y","n")

I am copying and pasting your formula. Is it because your format is different? My format is mm/dd/yyyy hh:mm yours is dd/mm/yyyy hh:mm

08/31/2023 23:54​	y
08/31/2023 22:05​	y
08/25/2023 04:01​	n
08/31/2023 20:45​	y

 

[Fluff]

See post#5

 

[odonovanc]

See post#5

Still giving me a "Y" for 8/31/2023 10:05:00 PM

There is actually a lot of Y that does not belong

08/31/2023 23:54​	y
08/31/2023 22:05​	y
08/25/2023 04:01​	y
08/31/2023 20:45​	y
08/31/2023 21:06​	y
08/31/2023 19:41​	y
08/31/2023 19:51​	y
08/31/2023 20:30​	y
08/31/2023 19:49​	y
08/31/2023 19:35​	y
08/31/2023 17:49​	y
08/31/2023 06:13​	y

 

[Fluff]

All of those times are between 11pm & 7am, so should return y

 

[odonovanc]

All of those times are between 11pm & 7am, so should return y

08/31/2023 22:05 That is 10:05 PM.

08/31/2023 21:06 That is 9:06 PM