Automatic generation of duty roster

Roster-MAY.xlsxHi Gurus

I have an interesting challenge.
I have a list of people who do duty on a duty roster
they are categorised into the number of duties that they do in a month i.e. once, twice, three or four times a month
They are also categorised by weekly or weekend duties as well as by specific time limitations.

Does anyone have any smart ideas of a way to generate this duty roster automatically on a monthly basis by taking the above criteria into account.  We are currently doing it in excel and it is tedious.  I have attached an example.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
This is one of those problems where there is no simple solution.

Does the duty need to be fulfilled by someone? no matter what?  or is it simply a matter of each person having to perform their tasks?  Seems to be the former from your question, but want to be sure.

caandalAuthor Commented:
Hi Jim
It has been a while - hope that you are well.  I agree that there is no simple solution.  The dates and times will be input manually but the trick is getting the system to allocate people to the dates and times without duplicating.  These dates and times are random and will change every month.  There will be four individuals allocated to every active shift.  The application in this instance is our Neighbourhood Watch.  We are trying to stamp out petty crime in the area which has been pretty active in the last couple of months.
many thanks
caandalAuthor Commented:
Sorry I did not answer your question correctly.  The duties should be filled by people no matter what but having said that we do juggle people from time to time according to their commitments
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

caandalAuthor Commented:
Jim the lines that I am think along are these.

1.  Generate a Shift table with Date and times for each shift as well as a week number i.e. week 1 week 2 and so on..
2.  Generate a People table with names and a number next to each name which will represent the number of shifts per month
3.  Populate the shift table with Names according to category (number of shifts) using a loop statement.  Part of the loop statement will do a check on the number of times that person will be allocated and then decrease that number in the people table each time the name is allocated.
You would Start your loop populating people who have the most duties allocated i.e.4 and then pick up others as the no of shifts decrease as you go.
Your thoughts
Dale FyeOwner, Developing Solutions LLCCommented:
1.  Do you have more than one "role" that has to be fullfilled?

2.  Are all of your people qualified to perform whatever shift you are trying to fill?

3.  Are all of your people qualified to fill all of the shifts?

4.  Are the shifts consecutive (24 hours a day).  If so, can the same person be assigned to two consecutive shifts?  Can some people only be assigned to a particular shift?  Do people have to coordinate their own swaps if they cannot work a shift they have been assigned for some reason?

5.  Do the total number of shifts eligible (sum of the shifts assigned to each person) equal your shift requirements, or are you going to have some that don't get as many shifts as they are eligible for?

I think you are generally on the right track with the concept you mentioned above where you start by sorting your people by the number of shifts they can work.  You then loop through those with the most number of shifts, subtracting one from their eligibility number.  When that group has each been assigned, you then get the group of people with the next highest number of shifts available and assign them, and continue this process.
caandalAuthor Commented:
Hi Dale

Anyone can do any shift.  There are no roles as such.  The shifts do not take place every 24 hours.  No person should do more than one shift per week.  Hence my suggestion of week 1, week2 etc...

We do not have enough shifts or enough people at this stage but is is growing.  Once the list is circulated it is up to people to co-ordinate their own swop-outs
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<Your thoughts >>

 That's it exactly.   You really can't do better than a trail and error approach.   The only thing I wondered about was seniority and such.  

 In your case, I would randomize the pool of people, then fill the slots.  Then at the end, I would run through the schedule and look for any uncovered periods.

 If you find one, then I would look at is the people already assigned, see if any can fill the slot if they switched, and if so, is there anyone left that could take the slot they vacate.  If yes, perform the swap.   If no, then continue with the next person already assigned.

 Of course there are many ways to do this, but if the primary goal is to completely fill the schedule, then this seems like a logical approach.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
caandalAuthor Commented:
Thanks for your inputs.  I have awarded points on the basis of you both taking the time to think about it and comment.

Many thanks
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.