Rotating Shift Schedule w/Multiple Stipulations

[erobinson]

Good morning,

I am trying to create a weekly on-call schedule. There are 12 on-call spots that must be filled each week. The first 11 spots will be filled from a list of 21 people (MEOs) and the 12th spot must have a person from a list of 5 people (Pumphouse). The goal is that no one is on-call 3 weeks in a row. Is this mathematically possible and if so, how do I create this in Excel?

 

[wideboydixon]

Just cycle through the employees in order. If you can use codes MEO1-MEO21 for the first 11 slots and PH1-PH5 for the 12th slot then this:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1	Slot	Week 1	Week 2	Week 3	Week 4	Week 5	Week 6	Week 7	Week 8	Week 9	Week 10	Week 11	Week 12	Week 13	Week 14	Week 15	Week 16	Week 17	Week 18	Week 19	Week 20	Week 21
2	1	MEO1	MEO12	MEO2	MEO13	MEO3	MEO14	MEO4	MEO15	MEO5	MEO16	MEO6	MEO17	MEO7	MEO18	MEO8	MEO19	MEO9	MEO20	MEO10	MEO21	MEO11
3	2	MEO2	MEO13	MEO3	MEO14	MEO4	MEO15	MEO5	MEO16	MEO6	MEO17	MEO7	MEO18	MEO8	MEO19	MEO9	MEO20	MEO10	MEO21	MEO11	MEO1	MEO12
4	3	MEO3	MEO14	MEO4	MEO15	MEO5	MEO16	MEO6	MEO17	MEO7	MEO18	MEO8	MEO19	MEO9	MEO20	MEO10	MEO21	MEO11	MEO1	MEO12	MEO2	MEO13
5	4	MEO4	MEO15	MEO5	MEO16	MEO6	MEO17	MEO7	MEO18	MEO8	MEO19	MEO9	MEO20	MEO10	MEO21	MEO11	MEO1	MEO12	MEO2	MEO13	MEO3	MEO14
6	5	MEO5	MEO16	MEO6	MEO17	MEO7	MEO18	MEO8	MEO19	MEO9	MEO20	MEO10	MEO21	MEO11	MEO1	MEO12	MEO2	MEO13	MEO3	MEO14	MEO4	MEO15
7	6	MEO6	MEO17	MEO7	MEO18	MEO8	MEO19	MEO9	MEO20	MEO10	MEO21	MEO11	MEO1	MEO12	MEO2	MEO13	MEO3	MEO14	MEO4	MEO15	MEO5	MEO16
8	7	MEO7	MEO18	MEO8	MEO19	MEO9	MEO20	MEO10	MEO21	MEO11	MEO1	MEO12	MEO2	MEO13	MEO3	MEO14	MEO4	MEO15	MEO5	MEO16	MEO6	MEO17
9	8	MEO8	MEO19	MEO9	MEO20	MEO10	MEO21	MEO11	MEO1	MEO12	MEO2	MEO13	MEO3	MEO14	MEO4	MEO15	MEO5	MEO16	MEO6	MEO17	MEO7	MEO18
10	9	MEO9	MEO20	MEO10	MEO21	MEO11	MEO1	MEO12	MEO2	MEO13	MEO3	MEO14	MEO4	MEO15	MEO5	MEO16	MEO6	MEO17	MEO7	MEO18	MEO8	MEO19
11	10	MEO10	MEO21	MEO11	MEO1	MEO12	MEO2	MEO13	MEO3	MEO14	MEO4	MEO15	MEO5	MEO16	MEO6	MEO17	MEO7	MEO18	MEO8	MEO19	MEO9	MEO20
12	11	MEO11	MEO1	MEO12	MEO2	MEO13	MEO3	MEO14	MEO4	MEO15	MEO5	MEO16	MEO6	MEO17	MEO7	MEO18	MEO8	MEO19	MEO9	MEO20	MEO10	MEO21
13	12	PH1	PH2	PH3	PH4	PH5	PH1	PH2	PH3	PH4	PH5	PH1	PH2	PH3	PH4	PH5	PH1	PH2	PH3	PH4	PH5	PH1
Sheet1

Cell Formulas
Range	Formula
B2	="MEO"&MOD(ROW()-2+(COLUMN()-2)*11,21)+1
B13	="PH"&MOD(COLUMN()-2,5)+1

WBD

 

[erobinson]

Thank you for the quick response however this method results in some MEOs being on-call in spots 1 & 2 multiple times while others are never in 1 & 2. When calling staff in, it goes in order so we'd like to sort of evenly distribute how the 21 people fall into the spots. I hope this makes sense but let me know if it doesn't.

 

[wideboydixon]

You'll have to point me to the row above that doesn't contain one of each MEO ...

WBD

 

[erobinson]

There is one of each MEO but spots 1-12 are called into work in that order. Therefore, certain MEOs are almost always in spot 1 or 2 when they are on-call which means they are likely to get called in more than others who may always be in spot 6 or 7 for example.