Taking randomly generated times, Returning "Daylight", "Nighttime" within a "time frame"

[Voz86]

Hi guys,

I have a column of randomly generated times using: =TEXT(RAND()*(23-10)/24+11/24,"HH:MM"). In the next column, using this formula, =IF(AND(F4>=1200,F4<=1800), "Daylight","Nighttime")
I want to see if the random time falls between a "time window" and return "Daylight" or "Nighttime." I can't get it to work, I also tried using "TIMEVALUE"

Any guidance would be greatly appreciated!

 

[Cubist]

Ideally, you want to keep it as number and just format the cell. Try

Book1
	A	B
1
2	20:31	Nighttime
3
Sheet9

Cell Formulas
Range	Formula
A2	A2	=RAND()*(23-10)/24+11/24
B2	B2	=IF(AND(A2>=0.5,A2<=0.75),"Daylight","Nighttime")

 

[Voz86]

Thanks for the response. How can I incorporate TIMEVALUE if I don't change TEXT(RAND) etc?

Thanks

 

[Cubist]

It's not necessary but if that's what you prefer

Book1
	A	B
1
2	11:42	Nighttime
3
Sheet9

Cell Formulas
Range	Formula
A2	A2	=TEXT(RAND()*(23-10)/24+11/24,"HH:MM")
B2	B2	=IF(AND(--A2>=TIMEVALUE("12:00"),--A2<=TIMEVALUE("18:00")),"Daylight","Nighttime")

 

[Voz86]

Awesome, that works! Final Q, why the -- before the cell number?

 

[LegendaryAxe]

"--" converts the text number to an actual number. See the example below.

Book1
	A	B	C	D
1	Values	Is Text?	Formulas for Column A	Formulas for Column B
2	22	TRUE	="22"	=ISTEXT(A2)
3	22	FALSE	=--A2	=ISTEXT(A3)
Sheet1

Cell Formulas
Range	Formula
A2	A2	="22"
B2:B3	B2	=ISTEXT(A2)
C2:D3	C2	=FORMULATEXT(A2:A3)
A3	A3	=--A2
Dynamic array formulas.

 

[Scott Huish]

I'm using a different formula but you don't need TIMEVALUE at all, you can use -- on all of them, but I prefer not having to do all these conversions as was suggested before.

=IF(MEDIAN(--A1,--"12:00",--"18:00")=--A1,"Daylight","Nighttime")

and with CUBIST's formula:
=IF(AND(--A1>=--"12:00",--A1<=--"18:00"),"Daylight","Nighttime")