Indentify Shift number in 24 hour period
 

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

Indentify Shift number in 24 hour period
 

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)

"mikebres" wrote:

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

Indentify Shift number in 24 hour period
 

One way...

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

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


--
Biff
Microsoft Excel MVP


"mikebres" wrote in message
...
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

Indentify Shift number in 24 hour period
 

Jason, thank you. That worked great!

"Jason" wrote:

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)

"mikebres" wrote:

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

Indentify Shift number in 24 hour period
 

Assuming shift start time in A1 try

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

"T. Valko" wrote:

One way...

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

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


--
Biff
Microsoft Excel MVP


"mikebres" wrote in message
...
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

Indentify Shift number in 24 hour period
 

All of these work great. Thank you folks.

"mikebres" wrote:

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