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!

 

[jasonb75]

Are the times moving past midnight when you add 7 hours? If so then try

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

In a formula, "22:00:00" + "07:00:00" is effectively "29:00:00", even though it would show in the cell as "05:00:00", it is actually 1 day and 5 hours, not just 5 hours (which the formula was looking for).

 

[supersam92]

Are the times moving past midnight when you add 7 hours? If so then try

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

In a formula, "22:00:00" + "07:00:00" is effectively "29:00:00", even though it would show in the cell as "05:00:00", it is actually 1 day and 5 hours, not just 5 hours (which the formula was looking for).

It works!
Amazing. Thank you very much!