Formula to find 'Part of the Day' from the Time Column

[Zee996]

Hi all,

I have Time data in Column D and I am looking for an Excel formula that will label the time as Part of the Day.

The formula that I am using right now only gives the "Morning Output"

Formula that I am using - =IF(OR(D2>=TIMEVALUE("5:00 AM"),D2<TIMEVALUE("12:00 PM")),"Morning",IF(AND(D2>=TIMEVALUE("12:01 PM"),D2<TIMEVALUE("5:00 PM")),"Afternoon",IF(AND(D2>=TIMEVALUE("5:01 PM"),D2<=TIMEVALUE("11:59 PM")),"Evening"))). Can someone please help? Thank you.

Here is how the data looks:

Time

12:20 PM

01:14 PM

02:02 PM

11:55 AM

12:27 PM

11:30 AM

10:37 AM

03:23 PM

09:05 PM

02:24 PM

08:42 AM

08:41 AM

08:40 AM

Here is the Criteria:

Criteria
Timings	Part of the Day
5:00 AM - 12:00 PM	Morning
12:01 PM - 5:00 PM	Afternoon
5:00 PM - 11:59 PM	Evening
12:00 AM - 4:59 AM	Early Morning

 

[LogChief]

=LOOKUP(
    B3:B15,
    HSTACK(
        VSTACK(TIME(0, 0, 0), TIME(5, 0, 0), TIME(12, 1, 0), TIME(17, 0, 0)),
        {"Early Morning"; "Morning"; "Afternoon"; "Evening"}
    )
)

 

[Micron]

But you can't have a value in 2 different slots (5PM)?

 

[LogChief]

=LOOKUP(
B3:B15,
HSTACK(
VSTACK(TIME(0, 0, 0), TIME(5, 0, 0), TIME(12, 1, 0), TIME(17, 0, 0)),
{"Early Morning"; "Morning"; "Afternoon"; "Evening"}
)
)

An alternative

 

[Zee996]

Thank you so much for the formula. It definitely works. I just checked and within the criteria there is two 5 PM. Would this formula work if I change the criteria?

Timings	Part of the Day
5:00 AM - 12:00 PM	Morning
12:01 PM - 5:00 PM	Afternoon
5:01 PM - 11:59 PM	Evening
12:00 AM - 4:59 AM	Early Morning

 

[Micron]

I can't really comment on the formula because they're not my thing so my capabilities there are quite limited. However, you could answer that by trying? I think I'd have the criteria like so:
05:00:00 AM - 11:59:59 AM
12:00:00 PM - 04:59:59 PM
05:00:00 PM - 11:59:59 PM
12:00:00 AM - 04:59:59 AM

 

[Zee996]

Thank you Logchief and Micron. I tried the exact formula just put (17, 1, 0) instead of (17, 0, 0) and this is giving me the correct output. Thanks a lot!

=LOOKUP(D2:D5483,HSTACK(
VSTACK(TIME(0, 0, 0), TIME(5, 0, 0), TIME(12, 1, 0), TIME(17, 1, 0)),
{"Early Morning";"Morning";"Afternoon";"Evening"}
)
)

 

[LogChief]

Thank you so much for the formula. It definitely works. I just checked and within the criteria there is two 5 PM. Would this formula work if I change the criteria?

Timings	Part of the Day
5:00 AM - 12:00 PM	Morning
12:01 PM - 5:00 PM	Afternoon
5:01 PM - 11:59 PM	Evening
12:00 AM - 4:59 AM	Early Morning

        TIME(0, 0, 0), TIME(5, 0, 0), TIME(12, 1, 0), TIME(17, 0, 0),
        {"Early Morning"; "Morning"; "Afternoon"; "Evening"}

You cannot change the wording order, and you cannot change the first time -TIME(0,0,0).
You can change the last three times, but they must be in sequential order. The LOOKUP function requires data to be in sequential or alphabetic order in the second argument.

 

[Zee996]

        TIME(0, 0, 0), TIME(5, 0, 0), TIME(12, 1, 0), TIME(17, 0, 0),
        {"Early Morning"; "Morning"; "Afternoon"; "Evening"}

You cannot change the wording order, and you cannot change the first time -TIME(0,0,0).
You can change the last three times, but they must be in sequential order. The LOOKUP function requires data to be in sequential or alphabetic order in the second argument.

Okay, understood. Thank you so much for your help.