Using excel solver to populate a staff rota

**Mdlooney** · 06-25-2016, 08:17 AM

I would be grateful for any help in populating a staff rota with employees using Solver.

The aim is to have the least number of staff working per day to cover 11 different areas (only one person needed to cover night shift at night)

The constraints are:
If employee works a night shift they don't work the following day
An employee works a maximum of one night shift per week
1 of the employees only works Thurs and/or Fri. If he works Thurs and Fri he also works the weekend.
If an employee works the weekend he doesn't work the following Monday
Each employee alternates between a 3 and 4-day week
However, if an employee works in one particular zone he does so for the entire week and has the following week off.
Employees take leave sometimes making them unavailable for work.

If it is possible to help with this problem I would be very grateful.

Thanks! Micheal

**Jacc** · 06-25-2016, 12:33 PM

If you attach a workbook (not pictures) with some data and the expected output it will be much easier to help you.

**Mdlooney** · 06-26-2016, 05:41 AM

Hi Jacc,

Thanks for your reply. I just uploaded the worksheet. When I mentioned that one employee works in zone 8 they do a 5 day week and don't work the following week.

Micheal