Thread: Shift Rotation

Im creating a worksheet for a 12-hr shift team rotation schedule. Each team has a schedule of W,R,O,O,D,D,N,N and back to W.

W=Work Rest
R=Rest
O=Off
D=Day Shift
N=Night Shift

The pattern always repeats itself every 8 days
What I wish to create is to have a date that always increase by 1 day automatically together with the shift pattern

There are a total of 4 teams and their schedule is as follows.

Team1
From 1Sep09 onwards =>N,N,W,R,O,O,D,D
Team2
From 1Sep09 onwards =>D,D,N,N,W,R,O,O
Team 3
From 1Sep09 onwards =>O,O,D,D,N,N,W,R
Team 4
From 1Sep09 onwards =>W,R,O,O,D,D,N,N

Hi,
can you be a bit more specific, maybe post a workbook with your data layout. Also, what is your data input? What do you expect Excel to do in which cells.....

deep breath ....
now that I've patiently downloaded a 600+kb workbook with 14 sheets brimming with formulae hidden as white on white, would you please care to point out what exactly you're after?

Can you please try to put yourself in my shoes? I don't know your workbook, I don't know what the problem is. I'm willing to help, but I need a bit more from you ...

So: what sheet, what input, where? what output, where? based on what logic in plain language?

Right, here we go. Some changes that do a lot for simplifying the whole workbook!!!

1. No need for an endless table with dates and rosters on the Team schedule. Instead, just one simple table that lists the repeat patterns for each team and is tied to a start date. I called this roster_lookup

2. in the monthly sheets, get rid of column O. You don't need it anymore.

3. Enter the date directly in the monthly sheets in column A. You can enter just the first of the month and then use a formula to add 1 to the cell above

4. the shift pattern is now calculated directly in the cell where the shift letter code appears. it is based on three variables:

- the date in column A
- the starting date for the roster sequence (range name = anchor). this is defined on the Team Schedule tab
- the team number of the staff member

it works like this

=VLOOKUP((MOD((A5-anchor)/8,1)*8)+1,roster_lookup,'Personal Data'!$C$6+1)

The blue part of the above calculates the differnece in days between the row's date and the anchor date. The result is divided by 8 (because you have 8 items in your roster sequence) and only the part after the decimal point is kept and multiplied by 8. This gives us a number from 0 to 7, to which we add a 1, so we end up with a number between 1 and 8 for any given date starting from the anchor date.

We use this number as the search reference for a VLOOKUP, so, if the number calculated for the 2-Sep is 2, then the formula would basically be

=VLOOKUP((2,roster_lookup,'Personal Data'!$C$6+1)

We want to find that number in the range called roster_lookup, which is on the Team Schedule sheet. The range has 5 columns. The values for team 1 are in column 2, team 2 is in column 3 and so on, so we need to take the team number and add a 1 to get the column number we need.

So, if the team number is 1, our formula now basically is

=VLOOKUP((2,roster_lookup,2)

which means: find the number 2 in the first column of the roster lookup range and return the value in the second column of the range, which is a "N"

Take some time to read through this and follow the logic in the attached spreadsheet.

get back if you have questions.

cheers