Solver to determine No. of workers required over a period of time

Sorry to say so, but I don't think the built-in solver will be OK, as there is a limit of ca. 200 variables, while here you have ca 90 days and may need well above 500 staff.

Moreover some additional rules shall be described.
For instance: what if a person works for 2 days and then have a free day. Shall he/she have 7 free (just after working 2 days) or can have 1 or 2 free and then work for instance 10 days and only then have 7 days free. And so on.
I assume that it is possible to work some time on day shift and then (after 7 days break) on night one. But can it happen after 6.5 or 7.5 days (expect the second is required)?

Does this help or offer a way forward. I've added a couple of helper rows. It assumes that the project starts with all staff having 14 days available.

Solver may be enough if we assume that there are 21 fixed schedule types.
Schedule A is 14/7, B is 13/7/1, C 12/7/2 and so on. In other words, each schedule after the first is shifted of 1 day, but has the same pattern for all 98 days.
Our objective is finding how many workers must follow each schedule, and minimize the total workforce while having everyday at least the required workers.

If this is enough for a start, check the Solver setup (and solution) in the file attached. You need at least 729 workers.

HTH,

Francesco

Thank You Hydraulics,

Your solution is spot on and does exactly what I need. Just a question, how did you arrive at the number of workers in A3:A23 or is this where you manually plan how many workers you want to add on a particular schedule?

Really appreciate the assistance.

Kind Regards

Coenie

Originally Posted by Coenieh

Just a question, how did you arrive at the number of workers in A3:A23 or is this where you manually plan how many workers you want to add on a particular schedule?

Cells A3:A23 are the variables, Solver finds the values for us.

This is really a minimum setup: one constraint linked to covering the demand in each day, and one to force the variables to integers. Moreover, it is based on a very strong assumption about scheduling.

It's not clear to me if this is a real world problem: I would expect both more constraints (for instance, we cannot have too many idle workers) and flexibility (scheduling must respect at least a 14w/7b cycle, but a worker may follow a 5w/12b/9w), and some economic reasoning (minimize costs).
As Kaper wrote, Solver (and even OpenSolver, a free and much powerful add-in) will not be enough, in general, to solve exactly the problem.

HTH,

Francesco