Between Time range return Days, Afternoon, Night

[kevin440red]

I have different times in Column (A), what i and trying to do is.

If the Time is between 6:37am and 2:36pm return Days
If the Time is between 2:37pm and 10:36pm return Afternoon
If the Time is between 10:37pm and 6:36am return Nights

Can someone help?

thanks

 

[FormR]

Hi, here is one option you could try:

Excel 2013/2016
	A	B
1	06:36	Nights
2	06:37	Days
3	14:36	Days
4	14:37	Afternoon
5	22:36	Afternoon
6	22:37	Nights
7	05:00	Nights
Sheet1

Cell Formulas
Range	Formula
B1	=LOOKUP(A1,0+{"00:00","06:37","14:37","22:37"},{"Nights","Days","Afternoon","Nights"})

 

[steve the fish]

Slightly different:

=LOOKUP(MOD(A1,1),{-1E-99,"6:36:59","14:36:59","22:36:59"}+1E-99,{"Nights","Days","Afternoons","Nights"})

 

[ie1002]

An alternative to the above if you prefer not to do a lookup would be a nested IF/AND formula like this:

=IF(AND(A2>=TIME(6,37,0),A2<TIME(2,36,0)),”Day”,IF(AND( etc...

 

[kevin440red]

All answers helped me.

Thank you all

 

[kevin440red]

Slightly different:

=LOOKUP(MOD(A1,1),{-1E-99,"6:36:59","14:36:59","22:36:59"}+1E-99,{"Nights","Days","Afternoons","Nights"})

for my education can you explain the formula above?