I am putting together a spreadsheet that will automatically schedule shifts for a staff of 18 people. I have put a considerable amount of effort into working out the basics, but the programming side of things is proving a bit more tough that i had anticipated.



i have some experience programming with python, and i have been trying to get through a 6 hour Excel video tutorial in order to get some hints as to how to tackle this problem.

i have tried to put together what seems like a possible solution to the problem-

Guidelines:
everyone should have a roughly (plus or minus 1 or 2) equal number of duties for each shift (eg. weekend primary or weekday secondary) so that no one person, for instance, is serving 3 more weekend secondaries than any other person.
no person may have weekend duties on 3 consecutive weekends (Friday or Saturday)
no person may have duties on two consecutive days
no person may have duties on a date listed under "Dates to Avoid" in the "Names and Dates" sheet
(obviously) no person may serve two duties on the same day (primary, secondary, or tertiary)

Strategy:
The person with the first lottery number will be selected to have a date assigned to a shift
If the date interferes with that persons "Dates to Avoid", that person will be skipped
If the person has a duty the day before the selected, they will be skipped
If the date is a Friday or Saturday, it cannot be assigned if the person has duty on both of the two previous weekends, that person will be skipped
If the person already has a duty on that day (on another shift), they will be skipped
once that whole table has been filled (eg. Every day in the 'dates pool' has been filled for W.Day Primary), the next shift will be populated (eg. W.Day Secondary)

just to explain the background,
basically, every weekday (Sun-Thu) two shits are needed (Primary and Secondary) and every weekend (Fri and Sat) three shifts are needed (Primary, Secondary, and Tertiary).

if anyone can help in ANY way, it would be much appreciated, even if its just a point in the right direction.

(it was made with excel 2007, but i have 2010 if anyone is so inclined)

Attached Files

Fall 2010 Duty Dispersion.xlsx.zip (18.6 KB, 24 views)