If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Allocate Time to a Shift
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. |
#2
|
|||
|
|||
Allocate Time to a Shift
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 -----Original Message----- 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. . |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
time zone & apt times auto changing | Mary | Calendar | 6 | May 25th, 2004 05:44 AM |
Calculating (Date and Time) differences | Frank Kabel | Worksheet Functions | 2 | April 27th, 2004 11:19 PM |
Calculating (Date and Time) differences | Madcap | Worksheet Functions | 0 | April 27th, 2004 08:56 AM |
time calculation | David McRitchie | Setting up and Configuration | 2 | March 28th, 2004 09:14 PM |
Calculating hours between a shift time to include core hours only | Darren Elsom | Worksheet Functions | 2 | March 20th, 2004 07:08 PM |