caandal
asked on
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.
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.
ASKER
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
Alan
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
Alan
ASKER
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
ASKER
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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
Alan
Many thanks
Alan
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.
Jim.