Allocate Time to a Shift

[john]

I have one unit of time and three shifts covering a 24
hour span. The shifts can vary - for example shift 1
would be from 12 midnight to 8am, shift 2 from 8am to 4pm,
and shift three from 4pm to 12 midnight. The next week
the shifts might change to shift 1 -> 2pm - 10pm, shift 2 -

10pm - 6am, and shift 3 -> 6am - 2pm.

I'm trying to create a formula or worksheet function that
would allocate a single unit of time into one of the three
shifts while allowing the shifts to vary. For example,
using the first scenario above if the unit of time was 1am
the formula would return "shift 1". Then when the shifts
change for the following week the same formula would
return "shift 3".

I can build a formula that splits the shifts but I'm hung
up on how to build it to allow for varying shifts. Any
help is appreciated. Thanks.

 

[Biff]

Hi John!

Yoi!

The only way I can see to accomplish what you want is to
define your shifts on a weekly basis. Then you could build
a lookup table and use a simple lookup function.

Table
week1 week2 week3
12:00 AM 1 3 2
1:00 AM 1 3 2

Then you would enter a time and week# to return the shift:

A1 = 1:00 am
A2 = week1

="Shift"&" "&VLOOKUP(A1,C2:F10,MATCH(A2,C1:F1,0))
Returns: Shift 1

Don't know if this helps, but, there you go!

Biff