Using Excel to create a rota with 5 different shift times, rotating and according to teams

[kayjay46]

Hi all

This is my first post so go easy on me! I am not sure if I am trying to have my cake and eat it - but it's worth a go. If anyone can either help or at least point me in the right direction as to how to do it, I would be very grateful!

Scenario:
I work in a surgery that has 4 sites. Each of these sites has two teams which has 2 - 4 doctors within them. Each of these doctors work different days and sometimes, different parts of the day (e.g. morning only). I need to be able to do a rota that:

1. Based on their normal days and which team they are in, shows who is available on a given day within the week. We do a week ROTA at a time.
2. There are two parts to the rota. The orange element (see attached) is where they each have to be assigned FIRST (based on their team and their availability). The 2nd part - the GREEN part, is filled with whoever is left from ALL of the teams.
3. I then need to know who is left after all of the slots have been filled.
4. The day is broken in to 5 shifts (you will see on the attached what times they are). The "late shift" - 18:30 - 20:30 is rotated between the sites each week. So in week 1, STA Team will do the late, week 2, SCH Team will do the late and so on.
5. And then..... I need to take account of holidays as well!

The perfect result would then be if I can get Excel to fill in the rota for the week (or 8 weeks if possible - to allow for the rotation of the late shift between sites and teams) automatically based on each partners availability and whose turn it is to do the late shift! At the very least, I would like to fill in the ORANGE rota and just get excel to tell me who is left on that day that I can use to fill in the GREEN rota with.

I really hope that makes sense!

I have attached what I have done so far - but it's just a mass of information at the moment, but hopefully you can see what I am trying to do.

Any help much appreciated!

Thanks

Kim

[BlindAlley]

Hi Kim,

The way that I see it you have approx 18 different scheduling scenarios per person depending on their availability, these need to be broken down into a table of code numbers which can be used in IF Statements to determine who goes where {or not}. The 2nd part would be another table to list only available personnel. The 3rd part would be to use INDEX MATCH to look up the code, the shift, the department, the day, and the names.
It may sound daunting but you only need to work on 1 employee for 1 week, the rest would be a fill down, across etc. The 3rd part is rotation which is another kettle of IF.

I started a template showing the grid that needs to be completed.

Steve

[kayjay46]

That's brilliant Steve. I'll have a look at that today and see what's what. I understand enough about Excel to be able to figure out the IF Statements with this as the starting point. Thank you so much for the initial boost on this!

Kim

[BlindAlley]

The section in RED will need some idea of how the schedule will need to work, proper Teams what shift they would start on etc.
The 2nd section will only list the available employees that have been marked as a yes.

[kayjay46]

Hi Steve

Again - thanks so much for this. I can see what your doing here which is good. So, the only additional element really is that within each team, there are say, 3 GP's. Each of those GP's work on different days within that team. So I need to put the scheduling rules against each person rather than the teams. If that makes sense.

So I suppose, working from the bottom up:
1. I need to show availability of each person.
2. Then which team they belong to, and, as a result, which person can be used from within that team on a particular day (and which shift they are available for)
3. Then, where they are available, they can be slotted in to a particular rota element which has been allocated to their team.
4. Then, once that has been done for the day/week, who is left that is available on that day/week to be slotted in to the rotas/shifts for the separate hub.

Just to explain, there are 4 different sites and each of those sites run normal GP surgery clinics. Then, in addition, on one of the sites we run a hub clinic which is manned through out the day for telephone triage with whoever is left that is not manning the "normal" clinics on each site.

So I just need to get each of the individuals availability in this schedule i.e. the rules that apply for each day for each of the doctors.

Thanks again! You've given me a nice amount to look at and work through.

Cheers

Kim

[BlindAlley]

Hi Kim,

Correct, you are on the right track, the heavy work is upfront the more work here the less input needed on the actual schedule.

Regarding the info box, I would put them in this order: name, team, location, shift & availability. Thinking about the availability I would put two columns one for All Month and one for All week and one column per day starting with the 1st day of the week your schedule normally starts on. The idea being that once we get to working out the schedule formulas a check 'Y' in All Month would automatically fill in that GPs schedule for the entire month. A 'Y' in the week schedules a week. Placing an 'N' in any one day would cancel that but fill in the week schedule except for that one day.{add extra columns into the 'info' section as required}. Don't worry about wrecking any of the other formulas the start point is more important. Oh one more thing, at some point we will need a list of the rotation.

Well, that's today's plan anyway

Steve