Staff Calender - one person per day| Easy...Right?

Hi ksto11,

do you have sample file how you imagine your file look like at the end ?

Thanks
Lex

Here it is. Just a basic monthly calendar for the output. The second sheet would have the availability for each employee. Looking at it more, could this be done using if, ifthen, count, count if functions? I feel the formulas would get crazy long.

FloorDuty_availability.xlsx


Kyle

So this formula

=IF(Availability!C$3="y",Availability!$A$3,IF(Availability!C$4="y",Availability!$A$4,IF(Availability!C$5="y",Availability!A$5,IF(Availability!C$6="y",Availability!$A$6,IF(Availability!C$7="y",Availability!$A$7,IF(Availability!C$8="y",Availability!$A$8,IF(Availability!C$9="y",Availability!$A$9,"NEED")))))))

will populate the calendar, however I'll end up with all the same people. How would I include the min/max days parameter?

Hey,

I have to create macro for it, give me a few days to figure it out -

Cheers
Lex

have a look at this

http://www.excelforum.com/excel-gene...-calendar.html

Thanks for the Help! Still tinkering around.

Hi,

Attached sample rostering schedule, am able to make sure that a certain employee would not exceed the max days, however you nee to make sure to adjust their minimum days.

Cheers
Lex

I managed to get this configured as a solvable linear model using Solver only:

Note: Solver is crippled by default to handle a maximum of 200 variables. You have 7 staff x 31 days = 217 variables, so I cut the date span to 28 days.

See the attached file, and note the solver parameter and options settings (Excel 2007). To run it, go to Solver, you'll see the settings, just click Solve.

FloorDuty_cy.xlsx

solver1.JPG

solver2.JPG

Hi ciyangou

Just a short comment. As you have the constraint "$$21:$AC$21 = 1" you can set the taget cell to "Max" or "Min" if you like as the "$$21:$AC$21 = 1" will always ensure that the value in AD21 will be 28 as long as solver finds a solution to the problem.

Nice model by the way!

Alf

Hi Alf, I did notice that, the 28 target was just redundant. Thanks for your comment; and the rep!

Hi Cyangou,

That is a very good way of solving it! using problem solver. need to learn how to use that rather than using VBA!

Cheers
Lex

Thanks. It's easier than you might think. ksto11 has good instincts for suspecting that this could be solvable with the solver.

Cyiangou, you are awesome, that is exactly what I was looking for to get my head back into excel. 6 years since I used solver in school. Now that i'v had time to play around with the model, I was able to add constraints to limit 1 shift every 4 days, however trying to avoid someone working back to back days seems to require too many constraints. Below is what i'v added.

By using constraints for each person only working 1 day within a 4 day period, starting on day 1, then repeating this starting on day 2, logically this would keep at least 2 days in between shifts, however this requires too many constraints for solver.

Any idea how to minimize the amount of cells I need to use?

FloorDuty_Solver.xlsx

Hi ksto11

Try this version. I can get a feasible solution if we relax the constraint and allow up to 2 days worked per moving 5 day period.

If the 'Can work?' table was less restrictive, you can probably get the model solved for a 1 working day per 5 rule.

FloorDuty_cy3.xlsx

Aside from the solution given by cyiangou there is another solver option you could try.

Download the OpenSolver freebee developed by the University of Auckland, NZ.

http://opensolver.org/installing-opensolver/

Unzip the downloaded file to a folder of your choice.

Start excel and open your file “FloorDuty_Solver.xlsx”

Start Solver and check settings i.e. box “Make Unconstrained Variables Non-Negative” is ticked and “Simplex LP” is chosen as solving methode.

Select “Options” and check box marked “Ignore Integer Constraints” is unticked.

Close Solver and find the file “OpenSolver.xlam”. Double click on this file and you will get new icons to right of the original excel solver icon.

Click on the icon marked “Solve” and that’s all. And yes it works without any problems on your "exstended" model (modified shift duty).

As there is no artificial limits set on the size of the problem you can solve you can extend you model size as much as you wish. I.e. increase time period to 180 days for instance.

The integration of OpenSolver in excel is temporarily, for a permanent integration of OpenSolver to excel see information in the “Download & Install” tab.

Alf

This is fantastic Alf. How many years I've searched for an uncrippled free solver, I'd stopped looking. I will definitely try this out.

BTW, I've just posted another solver solution here:
http://www.excelforum.com/excel-form...ia-is-met.html

Good, I think you will like this solver version. It's much better than the excel one. So far I've only been using the linear OpenSolver model. There is one that works with non-linear problems so eventually I'll have a go at it.

Checked you other solver solution and of cource I could not resist making some comments. Hope you don't mind.

Thanks for the rep

Alf

Brilliant Alf! Loaded OpenSolver and BAM! Funny how now i am spending more time playing with this new Solver than I would have creating the calendar by hand. This really saved me a lot of time though and headaches from staring at a calendar full of crossed out names. Thanks again cyiangou, Alf, and Excelforum.com. Hopefully I can return the favor at some point.

kyle

A pleasure Kyle. If you liked our contributions, please click 'Add Reputation' to the left of the persons' posts. You can also mark the thread as SOLVED using Thread Tools at the top. I had fun with this.

You are welcome.

Thanks for feed back and rep.

Alf

Sorry! Forgot you already gave! Thanks.