Assigning numbers to corresponding time durations on Google Sheets

user_19222195 0
Viewed 13

I am trying to assign the numbers 1-6 for various time durations.

For example if the duration is 00:01 (1 minute) to 04:00 (4 hours), I want a number designation of "1."

04:01 to 08:00, I want a designation of "2."

08:01 to 12:00, I want a designation of "3."

12:01 to 16:00, I want a designation of "4."

16:01 to 20:00, I want a designation of "5."

20:01 to 24:00, I want a designation of "6."

I have done some research into writing a formula like this, but have not had any luck. Thank you for you time!

google-sheets-formula duration time
1 Answers

try:

=ARRAYFORMULA(IFNA(VLOOKUP(A1:A*1, {
"00:01:00"*1, "1.";
"04:01:00"*1, "2.";
"08:01:00"*1, "3.";
"12:01:00"*1, "4.";
"16:01:00"*1, "5.";
"20:01:00"*1, "6."}, 2, 1)))

enter image description here

user_56326290
Related