Shuffle array (Scheduling)

Hi all,

Good day!

I have a scheduling related problem.

I am doing manual scheduling for a team of 14 people.
What I did is to play around the shift manually on a weekly basis, subject to the constraints of 5 working days per week (off days can be anyday) and 10 people working for each day.

Recently, the team requested, if possible, to have more shift patterns. It would be very time consuming for me if I would do the shuffling manually.

I came across this site:
http://www.ozgrid.com/forum/showthread.php?t=145450

but it's far more complicated than what I need.

I am hoping if anyone can help me or at least guide me to write a code on shuffling the working days and off days.

I uploaded one of the shift pattern that I currently have, which is done manually.
My constraints are only 2: 5 working days per week and 10 people working each day
Off days preferable, but not limited to, 2 days consecutively.

Appreciate any help.

Thank you very much!!

Appreciate if anyone can help?
Thank you!

Perhaps solver could be of help?

Have build a small model, info for solver found in file.

Alf

Your manpower loading lends itself very nicely to 7 groups of 2 people each. Each group would have two different consecutive days off.

It would be very easy for 2 people to switch shifts for a week and maintain the integrity of your schedule.

To vary things even more, you could (randomly or some other method) switch the ordering of the people which would create even more variation.

Lewis

Hi Alf,

Thank you for the suggestion.

Already add-in solver as suggested. However, this is new to me and I have difficulties in adding the contraints.
It either gave me all cells=5 or all cells=10
I don't really understand the logic of adding the constriants, can you explain to me?

Also, what if i want to add in more contraints like different type of shifts (e.g. day shift/night shift) represented by different number?

Thank you very much!!

Hi Lewis,

I thought about that, however, the pattern, or I should say the off days would be fixed on certain days no matter how I rotate them, no?
I might have a blind spot here... hm...

Group 1 (2 people) - Off Sunday and Monday
Group 2 (2 people) - Off Monday and Tuesday
Group 3 (2 people) - Off Tuesday and Wednsesday
Group 4 (2 people) - Off Wednsesday and Thursday
Group 5 (2 people) - Off Thursday and Friday
Group 6 (2 people) - Off Friday and Saturday
Group 7 (2 people) - Off Saturday and Saturday

Maintain the above schedule for a set period of time (1 or more weeks).
Group 1 becomes Group 2, Group 2 becomes Group 3, etc. for next time period.

Lewis

A solver model normally have a target cell, a range of cells that can be changed to reach the target and a number of constraints telling solver what it can and can’t do in order to reach the target.

This model has no target cell but it has a range of cells it can change (range D4:J17) and 3 constraints

1St constraint, all cells in range D4:J17 must be binary i.e. 0 or 1.
2nd constraint the sum of each row in yellow rectangle must be 5.
3de constraint the sum of each column in yellow rectangle must be 10.

So if 0 represent an “off day” and 1 represent a working day then person 1 will have Monday off, work “Tue” and “Wed”, off on “Thu” and work “Fri”, “Sat” and “Sun”

Total number of workdays for person 1 is summed in cell L4 i.e. 5 days

The solver constraint looks like this “$L$4:$L$17 = 5” and this ensures that each person does 5 workdays in a week.

Since the workload requires 10 persons to be working every day the sum of people working on say “Monday” must be 10 and the sum value is found in cell D19.

This constraint is set as “$D$19:$J$19 = 10”

Conclusion solver can change all numbers in range D14:J17 in any way is wish as long as
The numbers are binary, the sum of numbers in every row must be 5 and the sum of numbers in the columns must be 10.

And the sum formulas in range L4:L17 and D19:J19 is where solver checks these constraints.

I’ve also uploaded a more complex solver model about work distribution.

Alf

Hi Lewis,

Thank you for your suggestion.
Maybe I can start to explore from there, hopefully can find a way to automate the process.

Thank you!

Hi Alf,

Thank you so much for the explaination and the uploaded workbook as well!

Will explore from here and hopefully i am able to add in more complex constraints.

Thank you!

Thanks for feedback and rep

Best of luck in setting up a solver model that suits you needs.

In the office folder there is a folder called "Samples" and this contains a file "Solvsamp.xls" with different solver models and one model is about "Staff scheduling" or try a google search for "staff scheduling + excel solver"

Alf

Thanks for the rep points hcyeap. See the attached file that includes the code below. There is an option on the spreadsheet to allow you to choose the first OffDay for row 1. After that the other rows follow in sequence.

You can modify the lines at the top of the code to change the location of items.

Please Login or Register  to view this content.

Lewis

Hi Lewis,

Thank you very much! That is impressive.

Have a quick look but I admit I have to spend more time to understand the coding.

Really appreciate your effort! Thank you!

Regards,
HC