Indentify Shift number in 24 hour period

[mikebres]

Hi all, I am trying to find a way to identify the shift of a particular time
without the date. For example the shifts start at these times:
1 22:30
2 06:00
3 15:00

I would like a formula to show the shift number based on the time in the
spreadsheet, example:
Time Shift
06:36 2
07:03 2
11:13 2
....
15:45 3
17:56 3
20:27 3
....
23:06 1
01:30 1
05:55 1

What's is really frustrating is I found a formula to do this a few months
ago, and I just spent the last hour trying to find it again without any luck.

Thanks
Mike

 

[Jason]

Hi Mikebres,

The hourly system in Excel is based on fractions of 1 with 23:59 being
slightly less than 1, so based on your times, you could write a nested if
formula

=IF(C4<0.25,1,IF(C4<0.625,2,IF(C4<0.9375,3,1)))

Or if you want to be really fancy about it and allow yourself to change the
shifts you could set up a small shift list and reference those cells instead
of using the constants in the formula above (.25=6:00, .625=15:00, and .9375
= 22:30)

 

[T. Valko]

One way...

G1 = 22:30
G2 = 6:00
G3 = 15:00

=IF(A1="","",IF(OR(A1>=G1,A1<G2),1,IF(AND(A1>=G2,A1<G3),2,3)))

 

[mikebres]

Jason, thank you. That worked great!

Hi Mikebres,

The hourly system in Excel is based on fractions of 1 with 23:59 being
slightly less than 1, so based on your times, you could write a nested if
formula

=IF(C4<0.25,1,IF(C4<0.625,2,IF(C4<0.9375,3,1)))

Or if you want to be really fancy about it and allow yourself to change the
shifts you could set up a small shift list and reference those cells instead
of using the constants in the formula above (.25=6:00, .625=15:00, and .9375
= 22:30)

 

[daddylonglegs]

Assuming shift start time in A1 try

=LOOKUP(A1*24,{0,6,15,22.5;1,2,3,1})

 

[mikebres]

All of these work great. Thank you folks.