Hey everyone,

For my thesis I am currently working on a scheduling problem, the goal of my model is to find the optimal number of servers to meet demand over the course of a day and indicate what the schedule should look like (starting times for the different shifts). Opening hours for the deparment are between 06:00 and 23.00 (hourly periods: i=1,2,...,18).

I already formulated a model but when trying to solve the model with the excel solver it did not work, this appeard to be the problem of the solver not being able to work with if statements (countif etc). So I now have to reformulate my model to work with the excel solver and thats where I am stuck.

The model looks as follows:

With n being the maximum number of periods (i=18), ai = the number of agents starting their shift in period i, bi= the required number of agents in period i, ci = binary indicating if an shift starts in period i, M is the number of available agents and S= the maximum number of allowable shifts (usally 3 or 4).

I am not sure if this forum is the right place for this, but I hope someone can help me with this.

As said before the model needs to be able to produce a schedule for which number of agents used is minimized while still meeting the demand and allowable number of shift constraint. A shift is set at 8 hours (working the full 8 hours --> slight simplification) and since no part-time is used the no shift can start after period 11