Assign time to categories (morning, afternoon, etc)

**hwishman** · 03-28-2016, 01:29 PM

Hello,

Any suggestions for a formula that would take military time living in cell A2 and assign it to one of the categories below?

4:00 - 11:59 = morning
12:00 - 16:59 = afternoon
17:00 - 21:59 = evening
22:00 - 3:59 = night

Thanks for the help!

**zbor** · 03-28-2016, 01:33 PM

Try this:

Formula:

Please Login or Register  to view this content.

**dosydos** · 03-28-2016, 01:39 PM

This should also work

Formula:

Please Login or Register  to view this content.

**Tony Valko** · 03-28-2016, 01:42 PM

Create the 2 column table in D1:E5...

Data Range

	A	B	C	D	E
1	17:22	Evening		0:00	Night
2				4:00	Morning
3				12:00	Afternoon
4				16:00	Evening
5	------	------	------	22:00	Night

Then, this formula entered in B1:

=LOOKUP(A1,D1:E5)

**hwishman** · 03-28-2016, 01:50 PM

This works beautifully - thanks!

**hwishman** · 03-28-2016, 01:52 PM

=IF(AND(A2>=0.166666667,A2<=0.4993056),"Morning",IF(AND(A2>=0.5,A2<=0.7076389),"Afternoon",IF(AND(A2>=0.708333333,A2<=0.9159722),"Evening",IF(AND(A2>=0.916666667,A2<=0.1659722),"Night",""))))

Works great for all categories except for night for some reason which just returns a blank cell when formula is entered. I have midnight represented as 0:00, would that throw off the formula?

Much thanks for the help!

**Tony Valko** · 03-28-2016, 02:40 PM

You're welcome. Thanks for the feedback!

**hwishman** · 03-28-2016, 02:59 PM

All of the suggestions have worked! Thank you so much for your expertise :D

**roodzal** · 03-07-2020, 05:21 AM

= IF(HOUR(F2)>=20,"Night",IF(HOUR(F2)>=12,"Afternoon",IF(HOUR(F2)>=4,"Morning","Night")))

**maxoflagos** · 01-14-2022, 10:03 AM

This helped me, thank you!

Assign time to categories (morning, afternoon, etc)

LinkBack

Thread Tools

Rate This Thread

Display

Assign time to categories (morning, afternoon, etc)

Re: Assign time to categories (morning, afternoon, etc)

Re: Assign time to categories (morning, afternoon, etc)

Re: Assign time to categories (morning, afternoon, etc)

Re: Assign time to categories (morning, afternoon, etc)

Re: Assign time to categories (morning, afternoon, etc)

Re: Assign time to categories (morning, afternoon, etc)

Re: Assign time to categories (morning, afternoon, etc)

Re: Assign time to categories (morning, afternoon, etc)

Re: Assign time to categories (morning, afternoon, etc)

Thread Information

Users Browsing this Thread

Similar Threads

Determining Whether a Worked Shift Was Morning, Afternoon or Night

[SOLVED] assign values to categories

Morning, Afternoon and Evening

[SOLVED] Assign Categories for Time of Day: "Morning", "Afternoon", "Evening"

Good morning/afternoon all.

Good morning, afternoon or evening .... Yes, I'm new.

Shift planner - count the number of staff in the morning and afternoon

Bookmarks

Bookmarks

Posting Permissions