Excel Boffins, Help! Rota / Shift Roster

PilgrimToHyperion

PilgrimToHyperion

Expert Member
Joined
Jul 4, 2009
Messages
1,105
Reaction score
71
I'm hoping someone with some mad Excel and logical skills can help with building something in Excel to generate this instead of doing this by hand.

We have 3 types of shifts: Early, normal and standby. These are full day shifts. We're 6 guys that need to work these shifts. There can only be 1 person on early and 1 person on standby at any time. 1 specific guy does not work early and another specific guy does not work standby at all.

Is there some way to input the criteria into Excel and generate a shift roster somehow? Perhaps even have the option to add or remove someone's name and let the spreadsheet update accordingly.

Just to further clarify: Work 1 week early, work 4 weeks normal, work 1 week standby, repeat.
 
View attachment Shifts.xlsSo before you start with fancy formulas, this is what you want right? Is it correct that with the limitations on two of the guys, in my spreadsheet, Guy5 will have to work early twice (since he can work late) and vice versa for Guy 6?
 
Nope. The specific guys that don't work standby or early, don't work additional early or standby shifts.
 
So these two guys are on a 5 week cycle then while the other 4 are on 6 week cycles?
 
I cannot see that ever working if you need guys 1-4 to follow 1-4-1 cycle while guys 5-6 follow a 1-3-1 cycle. Unless you use a 30 week cycle where everybody does their allocated ****s, but some guys agree to work for example 1-2-1-2. So over a 30 week period, it would be fair but not 100% in sync.
 
Beylie said:
I'm hoping someone with some mad Excel and logical skills can help with building something in Excel to generate this instead of doing this by hand.

We have 3 types of shifts: Early, normal and standby. These are full day shifts. We're 6 guys that need to work these shifts. There can only be 1 person on early and 1 person on standby at any time. 1 specific guy does not work early and another specific guy does not work standby at all.

Is there some way to input the criteria into Excel and generate a shift roster somehow? Perhaps even have the option to add or remove someone's name and let the spreadsheet update accordingly.

Just to further clarify: Work 1 week early, work 4 weeks normal, work 1 week standby, repeat.
Click to expand...

Say you have guys A,B,C,D,E,F.

A doesn't work early, B doesn't work standby, so you have 3 sets:
Early: {B,C,D,E,F}
Normal: {A,B,C,D,E,F}
Standby: {C,D,E,F,A}


You could simply rotate through the sets for early and standby such that Week 1:
Early: {B,C,D,E,F}
Standby: {C,D,E,F,A}

B works early, C works standby. The other 4 guys work the normal shift.

Week 2:
Early: {C,D,E,F,B}
Standby: {D,E,F,A,C}

C works early, D works standby. The other 4 guys work the normal shift.

Week 3:
Early: {D,E,F,B,C}
Standby: {E,F,A,C,D}

D works early, E works standby. The other 4 guys work the normal shift.

Week 4:
Early: {E,F,B,C,D}
Standby: {F,A,C,D,E}

E works early, F works standby. The other 4 guys work the normal shift.

Week 5:
Early: {F,B,C,D,E}
Standby: {A,C,D,E,F}

F works early, A works standby. The other 4 guys work the normal shift.

Week 6 (cycle complete):
Early: {B,C,D,E,F}
Standby: {C,D,E,F,A}

B works early, C works standby. The other 4 guys work the normal shift (back to start).

[-]The only problem is that people work from standby to early, which you may want to avoid. (Just scramble the early and standby sets, making sure that no indices overlap and have the same worker assigned, i.e. worker A cannot be in the same position in both early and standby sets).[/-] Scratch that, it's by week not day so this is probably fine.
 
Last edited:
Guys 5 and 6 works 5 normal shifts instead of 4. So you'll have this:

Guys 1 -4
Early, normal, normal, normal, normal, standby

Guy 5
Early, normal, normal, normal, normal, normal

Guy 6
Standby, normal, normal, normal, normal, normal

The order of the shifts are not important. If it happens that guys 1-4 works additional normal shifts along the line to allow guys 5 and 6 to work their early or standby shifts, it's fine. This roster isn't going to be completely fair because of guy 5 and 6.

I'm not very good at explaining this, am I? :wtf:
 
Beylie said:
Guys 5 and 6 works 5 normal shifts instead of 4. So you'll have this:

Guys 1 -4
Early, normal, normal, normal, normal, standby

Guy 5
Early, normal, normal, normal, normal, normal

Guy 6
Standby, normal, normal, normal, normal, normal

The order of the shifts are not important. If it happens that guys 1-4 works additional normal shifts along the line to allow guys 5 and 6 to work their early or standby shifts, it's fine. This roster isn't going to be completely fair because of guy 5 and 6.

I'm not very good at explaining this, am I? :wtf:
Click to expand...

But isn't that what I did?
 
If an additional guy is added to guys 1-4? Sigh... requirements are changing the whole time.

Normal: {A,B,C,D,E,F,G}?
 
Beylie said:
If an additional guy is added to guys 1-4? Sigh... requirements are changing the whole time.

Normal: {A,B,C,D,E,F,G}?
Click to expand...

You'll just have to add him/her to the early and standby sets as well, if applicable.

The early and standby sets are the ones that really matter (since everyone works the normal shift). Just cycle through them and make sure there aren't overlaps (same person in the same position in both sets).
 
I assume there needs to be some kind of order hence the requirement for this spreadsheet? Else it could have been applied randomly.
 
Well, the guys have been working shifts so this will need to adapted to be fair and at least slot in with where we currently are in the roster. Pretty much just moving the names around. Also, it won't be fair to work say 8 normal shifts and then 2 standby and 2 early right after each other.
 
Beylie said:
Well, the guys have been working shifts so this will need to adapted to be fair and at least slot in with where we currently are in the roster. Pretty much just moving the names around. Also, it won't be fair to work say 8 normal shifts and then 2 standby and 2 early right after each other.
Click to expand...

Who know what industry is this? An uncle of mine is in the security industry and their shifts are brutal and rather unfair. But yah, I agree with you if this was a fair company. :)
 
You must log in or register to reply here.
Back
Top
Sign up to the MyBroadband newsletter
X