# Setting up a staff rotation spreadsheet.

#### Darren Bartrup

##### Well-known Member
Morning all,

I'm trying to set up a shift rotation pattern for a team at work and I'm getting nowhere.

The basics are that four people in the team will work early shift, eight people will work late shift and everyone else in the team will work core hours.

The shift will change on a weekly basis and preferably work so that people won't go straight from an early to late shift, but rather spend time on the core hours before swapping to early or late.

So my question is - has anyone experience or an idea of how to set up a spreadsheet so that based on a week number, or a date (or anything that works) I will be able to sort out who's on what shift?

I've got about as far as splitting the team into groups of four (with a couple of stragglers as the team won't split exactly) with the idea of moving the groups into different shifts but just can't figure out my sums (why didn't I listen more doing maths at school!).

Any help would be greatly appreciated as always.

### Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use \$ signs: \$V\$2:\$Z\$99 will always point to V2:Z99, even after copying
Hi

How many teams of four.

Hi Saltkev,

It's one team which I'm splitting into groups of four people - just because four are needed to do the early shift, and two times four are needed for the late shift.

So if, as the case is currently, there are 39 people in the team then there's nine groups of four and one group of three.... not sure what to do with the group of three yet though - maybe they get lucky and don't do shift work until another member joins.

Hi Darren

I have manged to cobble something together based on 9 Groups of 4 employees, using a 9 week rotation. in any one week there will be one group on mornings, 2 groups on afternoons and 6 groups working core hours. Each shift is seperated by a stint on core hours. Unfortunatly I do not have the internet access required to post the result here. If you send me your e-mail address I will send you the file by return.

regard

Kev

mail to kevin.eames@airbus.com

I've managed to sort this problem out, with an idea from saltkev:

• There's two sheets in the work book.
• The first is called 'Workers' and stores the workers name and which group of four people they're in.
• Cell B3 has this formula which is dragged down and identifies the group number.
=ROUNDUP((ROW()-2)/4,0)
Column C has the worker name.
• There are two named formula which reference this sheet:
TotalGroups - =MAX(Workers!\$B\$3:INDEX(Workers!\$B:\$B,COUNTA(Workers!\$B:\$B)))
MaxGroup - =IF(COUNTIF(Workers!\$B:\$B,TotalGroups)<4,TotalGroups-1,TotalGroups)
The first counts the number of groups, and the second gives the maximum group that contain four people.
• The second sheet is called 'Shifts'
• The date is entered in cell B3.
• Cell C9 contains this formula which returns the Monday week commencing date.
=IF(WEEKDAY(\$B\$3,3)=0,\$B\$3,\$B\$3-WEEKDAY(\$B\$3,3))
• Cell E9 contains the formula
=C9+7
• Range B10:Bxxxx contains the group numbers 1 to MaxGroup.
• Cell C10 contains this formula which calculates the shift pattern and displays whether that group should be on Morning, Evening or Core hours. This formula is dragged across and down to create the full shift pattern for each week.
=IF(MOD(SUM(INT((C\$9-39083)/7)+1,\$B10-1),MaxGroup)+1=1,"Morning",IF(OR(MOD(SUM(INT((C\$9-39083)/7)+1,\$B10-1),MaxGroup)+1=3,MOD(SUM(INT((C\$9-39083)/7)+1,\$B10-1),MaxGroup)+1=5),"Evening","Core"))
• The number of people can be increased or decreased and the groups will adjust - just drag the formula down on each sheet.

I had a sub-topic on this while trying to figure out the groups:
http://www.mrexcel.com/forum/excel-...ximum-week-number-based-date.html#post3933811

Replies
2
Views
323
Replies
0
Views
146
Replies
0
Views
821
Replies
1
Views
1K
Replies
1
Views
5K

1,196,057
Messages
6,013,159
Members
441,751
Latest member
336448

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back