How can I categorize time?

[supersam92]

Hello, I need help please ?

I want to divide 24 hours into 4 categories:
00:00-09:14=1
09:15-11:59=2
12:00-17:14=3
17:15-23:59=4

Is there any way that I can turn a column of time into value 1/2/3/4?
E.g. 15:22 -> 3, 18:46 -> 4........

Thank you!

 

[mikerickson]

If your column of times is A:A, you could put this in B1 and drag down
=MATCH(A1, TIMEVALUE({"0:00:00","9:15:00","12:00:00","17:15:00"}))

 

[supersam92]

If your column of times is A:A, you could put this in B1 and drag down
=MATCH(A1, TIMEVALUE({"0:00:00","9:15:00","12:00:00","17:15:00"}))

Hi mikerickson, thanks for your speedy reply!
It seems not working, all cells become "0:00" after I input =MATCH(A1, TIMEVALUE({"0:00:00","9:15:00","12:00:00","17:15:00"})).
Do I need to assign 1,2,3,4 to the four time ranges other than just the Match function?
I have no ideas how to do this...

 

[CA_Punit]

I think your cell format has changed


=IF(A1="","",MATCH(A1, TIMEVALUE({"0:00:00","9:15:00","12:00:00","17:15:00"})))

I have posted the sheet through XL2BB
Please check

Book1
	A	B	C
1	00:00:00		1
2
3
4
5
6
Sheet1

Cell Formulas
Range	Formula
C1	C1	=IF(A1="","",MATCH(A1, TIMEVALUE({"0:00:00","9:15:00","12:00:00","17:15:00"})))

 

[supersam92]

Oh, it really is the problem of my cell format.
It works now!
Thank you! ?

 

[supersam92]

Sorry I got another issue.

If I input other time range, like this:
=MATCH(A1,TIMEVALUE({"17:00:00","2:15:00","6:00:00","10:15:00"}))
Some of the results become wrong.
e.g.
17:00:00 becomes 4 instead of 1,
Any time within 00:00:00-02:14:59 becomes #N/A.

Any advice please?
Does the Match TIMEVALUE formula need to start with "00:00:00"?
Thank you

 

[jasonb75]

Does the Match TIMEVALUE formula need to start with "00:00:00"?

No, but it needs to be in ascending order. For something like that you would need lookup instead of match, i.e.

=LOOKUP(A1,TIMEVALUE({"2:15:00","6:00:00","10:15:00","17:00:00"}),{2,3,4,1})

 

[supersam92]

No, but it needs to be in ascending order. For something like that you would need lookup instead of match, i.e.

=LOOKUP(A1,TIMEVALUE({"2:15:00","6:00:00","10:15:00","17:00:00"}),{2,3,4,1})

Cool! Thanks for big help!!

 

[jasonb75]

I should have been a bit more specific on my answer, it doesn't need to start with "00:00:00" if there will not be any times to match that fall between 00:00:00 and 02:15:00.

If you want times in that range to be included in the range of 17:00:00 - 02:15:00 then you will need to split that time window into 2 parts (before and after midnight).

=LOOKUP(A1,TIMEVALUE({"00:00:00","2:15:00","6:00:00","10:15:00","17:00:00"}),{1,2,3,4,1})

There are other ways of achieving the same, this is the closest method to that which you are already using.

 

[supersam92]

I should have been a bit more specific on my answer, it doesn't need to start with "00:00:00" if there will not be any times to match that fall between 00:00:00 and 02:15:00.

If you want times in that range to be included in the range of 17:00:00 - 02:15:00 then you will need to split that time window into 2 parts (before and after midnight).

=LOOKUP(A1,TIMEVALUE({"00:00:00","2:15:00","6:00:00","10:15:00","17:00:00"}),{1,2,3,4,1})

There are other ways of achieving the same, this is the closest method to that which you are already using.

Yes I just notice that too. Thanks!

And I'm trying another thing and it got me again...
I make a new column B for adding hours to the original time by =A1+TIME(7,0,0).
E.g. from 00:00:00 to 07:00:00
Then this formula gives wrong result again... =LOOKUP(B1,TIMEVALUE({"00:00:00","2:15:00","6:00:00","10:15:00","17:00:00"}),{1,2,3,4,1})
There are no more "2" showing and also some other values go wrong.
But if I manually type in the same time in the B cell, the result becomes correct again.
It's really strange to me...
Any ideas?