Rota system

[KevHardy]

OK. This is a tricky one (I think!).

Where I work we have a team of around 15 people, some of whom work part-time.
Each worker has to spend time on a duty rota as either Duty 1, Duty 2, or
Visiting Officer.

What I would like to do is have an excel spreadsheet with a month per sheet
across a year (for example – this might not be the best way to tackle the
problem).

I would like to be able to enter the names of the team members with the
hours/days they work (to take account of the part-timers and be able to cope
with changes to the team) and then for some clever mathematical equation to
work out the rota for me.

Each member of the team would need to do their fair share of days on duty
with each worker doing Duty 1, Duty 2 and Visiting Officer days spread across
a month. Of course, it may be that some workers do more duty days on some
months but this should be balanced out across the year as much as possible.

Is it possible?

 

[KevHardy]

Some more info -
A table showing the various combinations of days worked by different staff:

Mon Tues Wed Thur Fri
Worker 1 No No No Yes Yes
Worker 2 Yes Yes Yes Yes Yes
Worker 3 Yes Yes Yes Yes No
Worker 4 Yes Yes Yes Yes Yes
Worker 5 Yes Yes Yes Yes Yes
Worker 6 Yes Yes Yes Yes Yes
Worker 7 No Yes Yes No No
Worker 8 Yes Yes Yes No No
Worker 9 Yes Yes Yes Yes Yes
Worker 10 Yes Yes Yes Yes Yes
Worker 11 Yes Yes Yes Yes Yes
Worker 12 Yes Yes No No No

 

[KevHardy]

Perhaps I need to be more specific as there has been a deathly silence since
I posted this question

The macro would need to pre-fill a calendar with three different names for
each working day (not weekends and not bank holidays - would also need to be
able to input workers annual leave intot he equation) – someone as Duty 1,
someone as Duty 2, and someone as visiting officer.

This wouldn’t be a problem if all the workers worked 100% of the time, but
they don’t. For example, (as from the last post) worker 1 only works on a
Thursday and Friday so the macro would have to have some way of knowing this
and accommodating it. So the macro should not assign worker 1 to any of the
duty roles on a Monday, Tuesday, or Wednesday.

The other problem would be that as worker 1 only works 60% of the hours of a
full time member of staff it follows they should only do 60% of the duty
hours. And as you’ll see from the last post we have some workers doing 40% (2
days per week) and 80% (4 days per week).

I’m not particularly looking for someone to provide me with finished code
but some help and advice about how to tackle the problem would be most
welcome.