Setting up a staff rotation spreadsheet.

Darren Bartrup

Well-known Member
Joined
Mar 13, 2006
Messages
1,296
Office Version
  1. 365
Platform
  1. Windows
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

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
Hi Saltkev,

Thanks for replying.

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. :)
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
Solution

Forum statistics

Threads
1,213,534
Messages
6,114,185
Members
448,554
Latest member
Gleisner2

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top