Hi, I have a similar problem and tried to use opensolver but ran into issues. maybe the problem is too complicated for excel.
I'm trying to create a staff schedule where:
24/7 department
12 hour shifts (day and night)
max 3 consecutive nights
max 4 consecutive days
3 skill types that are random among staff
staff are Full time (6 shifts per 2 weeks minimum and soft maximum), part time (4 shifts per 2 weeks min and 6 max) or casual (6 shift/ 2 weeks max)
in model i have 15 employees (actual will be 50-60ish)
each shift has 1 person fill it for one day
weekends and holidays may not require certain shifts
each shift may require a specific skill
eventually need special rules for specific staff like less nights or no nights.
Approach:
I create a table with repeating dates for the column and the 2nd column i have every shift repeating
several other columns to show whether a shift is required for that date (if holiday or weekend)
then the row headers had every staff
body would be 1 or 0 to show the person taking the shift
constraints would be on separate sheets that use combinations of index/match and sumproducts to count skills, or shifts and check if they fit a rule.
objective is to have 1 shift filled by one person for every day (sum all of the different between actual and target) and aim for 0
Outcome:
Solver was supposed to fill in the table and comply with the constraints but it always goes crazy and tries to schedule a bunch of people in one row.
Any help or direction would be greatly appreciated.
Bookmarks