• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA to automatically populate a rota / shifts

S

Sam Longstaff

Member
HI,

I'm looking for a VBA solution to automatically populate an on call rota please.

The rota should be filled according to the following rules:
  • Only 1 staff member for each day
  • Staff member can't be on 2 consecutive days
  • weekdays are allocated separately from weekend days. This is to ensure fair distribution of weekend days but must be mindful of Saturdays following on from Fridays.
  • If the number of staff on the on call list (call_list) is divisible by five then the weekday allocation should be given in such a way that the days allocated roll i.e. staff member is not constantly on a Monday week to week but will be Monday one week, a different day the next etc. Where the list is not divisible by 5 there will be a natural roll to facilitate this.
  • If the number on the call list is divisible by 2 then the weekend days will need to be allocated in a way that facilitates a varied weekend day. If this is too much hassle then it can be omitted as it's easy enough to manually sort.
I have included a workbook that has my current method to achieve this.
  1. A date is entered in Rota_start ('Order of play'!A2) which then creates the date range in the rota worksheet.
  2. The name of the person to start the weekday rota is selected from the drop down list. As is the person for the weekend rota. These names would be the people following on from the tail end of the previous rota.
  3. Rota populates with names based on the cell formulas.
Whilst this workbook does what I am trying to achieve, it is limited because it works for the number of people on the call list.

I would like for a VBA solution that works dynamically should the number of people on the call list increase or decrease. Also the weekday and weekend rotas can be presented as separate lists as I'd be able to merge these after.
Is this possible?
 

Attachments

  • On call list workbook v2.xlsx
    26.3 KB · Views: 4
Sam Longstaff
How many Days - Weeks - Months -Years - should call rota should automatically populate?

Questions base Your rules:
#1 Only 1 staff member for each day
Where are staff members?
Could it be ... call list? or something else?
Are all staff member always available?
#3 Why two lists?
#4 If the number of staff on the on call list ...
Is that list fixed of what?
#5 What do You mean?

Is there somewhere a clear sample of expected output?
... please, skip that 'Rota'-sheet with Your reply.

Many things are possible with VBA too.
 
Last edited:
Sam Longstaff said:
Whilst this workbook does what I am trying to achieve, it is limited because it works for the number of people on the call list.
Click to expand...
This does not address all the constraints but it does as much as your formulae did but allows for different numbers of people; a small improvement.
In the attached, on sheet Order of play:
At cells E4:F4, two spilling formulae to account for different numbers of people on the call list.
(Your old formulae still present in columns L and M to allow your sheet Rota to continue working, for comparison.)
Data Validation in cells E2:F2 also accommodate changing numbers of people.

On sheet Rota (2):
Spilling formulae in cells A3, E3, I3 etc.
Formulae in columns B, F, J etc. These can be copied down.
Cells A3:B44 can be copied as a whole to other columns.
Working conditional formatting for the months.
Red highlighting on the first of cells where there are duplicate names (your Rota sheet doesn't prevent this either). Does not show duplicates on consecutive dates when theys are on the bottom of one column and the top of the next (although it's possible to do).

If I get the time I'll have a go at VBA.
 

Attachments

  • Chandoo50739On call list workbook v2.xlsx
    42.8 KB · Views: 5
Last edited:
vletm said:
Sam Longstaff
How many Days - Weeks - Months -Years - should call rota should automatically populate?

Questions base Your rules:
#1 Only 1 staff member for each day
Where are staff members?
Could it be ... call list? or something else?
Are all staff member always available?
#3 Why two lists?
#4 If the number of staff on the on call list ...
Is that list fixed of what?
#5 What do You mean?

Is there somewhere a clear sample of expected output?
... please, skip that 'Rota'-sheet with Your reply.

Many things are possible with VBA too.
Click to expand...



Hi Vletm,

Many thanks for your response and questions. To answer the questions:

1) Staff members are named in the call list. If their name is there, then they are available. This list of names may expand or contract based on the number of staff available.

3) The 2 lists are for weekdays and weekend days. This is to ensure a fair distribution of weekend days as these are less desirable than weekdays.

4) If total staff on the call list is 5,10,15 or 20 then this would naturally fill the rota Mon-Fri on repeat therefore staff member 1 would always be on a Monday. This is not ideal as the staff member should be allocated different days as the rota rolls over.

5) this is similar to point 4. For weekends staff should not always be on just a Saturday or Sunday. They need to be allocated a fair number of each.
 
Sam Longstaff
My sample has those features, which You answered.
There are other features too, which You skipped to answer.
There could be some features, which could add there too.
If You pressed that button, then there is that code too.
 
Sorry I missed the question about how long the rota should be. It's usually only 3 months but I guess it's good that your solution can show a longer period if needed.

I wish to be able to remove or add staff to the call list and the lists for weekdays/weekends to adjust automatically without gaps. Can this be done?
 
Sam Longstaff
It creates ... yes, longer list now.
... and ...
There could see, number of shifts per member.
If You could answer those missing questions, there could be ready that modify the call list feature - it's possible to do.
Why do You want to do Yourself that call list? There could be 'a calendar', which shows - who are available any day.
Of course, number of shifts per member should be in balance all the time - no matter number of members - or how?
What means Your automatically populate? When/how that should be happen? ... every time while You'll open that file - or how?
 
vletm said:
Sam Longstaff
It creates ... yes, longer list now.
... and ...
There could see, number of shifts per member.
If You could answer those missing questions, there could be ready that modify the call list feature - it's possible to do.
Why do You want to do Yourself that call list? There could be 'a calendar', which shows - who are available any day.
Of course, number of shifts per member should be in balance all the time - no matter number of members - or how?
What means Your automatically populate? When/how that should be happen? ... every time while You'll open that file - or how?
Click to expand...

Hi,

I'm not sure which questions I've not answered for you?

I'm happy for the rota to fill when I click the button (as you have created).

Why do You want to do Yourself that call list? There could be 'a calendar', which shows - who are available any day.
I think it's easy to just have a list of staff but if you can show how a calendar linked to it would work then that would be cool.
 
In the attached (there's quite a lot!):
  • The list of names you want to use is determined by the extent of the table at cell B1 (called call_list). You can adjust this by dragging the grab handle at the bottom right of the table without having to delete any names:

    82566
  • The cell E2 (green) is for you to choose who starts the list. This is the person at the top of the list, regardless of what day of the week that is. Adjust the starting date in cell A2 to a Monday if you want that day to be a Monday. I have not done similar for the person on the first Saturday. You can delete the entry in E2 altogether to leave a blank cell, so anyone may end up being the first person in the list. If you must have a certain person on the first Saturday as well, there is a degree of randomness built in to the routine and the routine is fast enough (whether for 10 days or for 10 years) to allow you to press the button(s) repeatedly, getting a new list until you see the person you want against the first Saturday.
  • There are several buttons on the sheet, all call the same routine.
  • The whole list is produced in columns K:L.
  • I have added a (temporary) formula in column J all the way down to row 3652; this is to allow pivot tables to analyse the distribution of names against days of the week.
  • There's conditional formatting in column L to show red if there are two names next to each other. It never shows.
  • Columns O:Q show a little table calculating the variance of the results in cell Q3. This needs to be as low as possible. I think it stays in acceptable limits all the time.
  • A pivot table at cell S2 which shows the distribution of names v days of the week. There is conditional formatting to show any empty cells, but it too never shows up because there never are any. It looks like this for a year:

    82573
  • Another pivot table at cell S14 which just shows individual's list of dates.
  • In columns AP:BO there are formulae in row 2 (highlighted) to show the results in the format you wanted.
  • There's an easier single-celled formula in cell BQ2:
    82575
    You need to remove the apostrophe from the start of the fomula to see what it does.
  • If you want to change how many days are produced, the 4th line in the macro: DaysCount = 365 is what decides that.

ps. vletm's solution, straight out of the box, shows this:

82574

which means that Cheryl and Fraser never work on a Saturday, Graeme and Jackie never work on a Sunday, etc.
 

Attachments

  • Chandoo50739On call list workbook v2c.xlsm
    113.6 KB · Views: 4
Last edited:
Sam Longstaff
A Calendar-version sample.
Quick instructions:
# Row 10 have all Your members.
# I-column has dates.
# If member is free to have ROTA then members cell eg with Cheryl / 01-Jan-2023 is blank (cell K11)
# If member has something eg Sam / 07-Jan-2023 WE (cell J17) then Sam won't have ROTA.
# You can select any day from I-column and press [ ROTA ]-button
# It will take care previous eg 28 days ROTAs to balance next eg 120 days ROTA-shifts
# If You would like to solve ROTAs again eg after You've made some modifications to calendar
>> select data and press [ROTA]
# Number of free members per day matters weight of row 7&8 values ...
# This is still a sample and I've done only quick tests with it.
Questions?

p45cal
which means that Cheryl and Fraser never work on a Saturday, Graeme and Jackie never work on a Sunday, etc.
One Question: Do above matter?
Same way, someone else could wonder from Your solution: Cheryl works four times Mon and Wed, Sharon works four times Wed ... etc.
 

Attachments

  • On call list workbook v2.xlsb
    26.6 KB · Views: 1
Last edited:
Sam Longstaff
If Sat & Sun really matters - then of course, it could take care.
As well as there could be option to set number of days between members ROTAs...
or/and
Number of history of ROTAs to balance those smoother.
or/and
Do number of free members per day matters weight of ROTA-value ( if some of members are absent then ROTA-member could get something like bonus )?
 

Attachments

  • On call list workbook v2.xlsb
    35.5 KB · Views: 2
p45cal said:
In the attached (there's quite a lot!):
  • The list of names you want to use is determined by the extent of the table at cell B1 (called call_list). You can adjust this by dragging the grab handle at the bottom right of the table without having to delete any names:

    View attachment 82566
  • The cell E2 (green) is for you to choose who starts the list. This is the person at the top of the list, regardless of what day of the week that is. Adjust the starting date in cell A2 to a Monday if you want that day to be a Monday. I have not done similar for the person on the first Saturday. You can delete the entry in E2 altogether to leave a blank cell, so anyone may end up being the first person in the list. If you must have a certain person on the first Saturday as well, there is a degree of randomness built in to the routine and the routine is fast enough (whether for 10 days or for 10 years) to allow you to press the button(s) repeatedly, getting a new list until you see the person you want against the first Saturday.
  • There are several buttons on the sheet, all call the same routine.
  • The whole list is produced in columns K:L.
  • I have added a (temporary) formula in column J all the way down to row 3652; this is to allow pivot tables to analyse the distribution of names against days of the week.
  • There's conditional formatting in column L to show red if there are two names next to each other. It never shows.
  • Columns O:Q show a little table calculating the variance of the results in cell Q3. This needs to be as low as possible. I think it stays in acceptable limits all the time.
  • A pivot table at cell S2 which shows the distribution of names v days of the week. There is conditional formatting to show any empty cells, but it too never shows up because there never are any. It looks like this for a year:

    View attachment 82573
  • Another pivot table at cell S14 which just shows individual's list of dates.
  • In columns AP:BO there are formulae in row 2 (highlighted) to show the results in the format you wanted.
  • There's an easier single-celled formula in cell BQ2:
    View attachment 82575
    You need to remove the apostrophe from the start of the fomula to see what it does.
  • If you want to change how many days are produced, the 4th line in the macro: DaysCount = 365 is what decides that.
ps. vletm's solution, straight out of the box, shows this:

View attachment 82574

which means that Cheryl and Fraser never work on a Saturday, Graeme and Jackie never work on a Sunday, etc.
Click to expand...

p45cal,

This is excellent and I think this could be the solution.

Is it possible to put the end date of the rota in A3 and then the macro automatically adjusts the number for the DaysCount?

Thanks for your input, totally amazing!
 
Sam Longstaff said:
Is it possible to put the end date of the rota in A3 and then the macro automatically adjusts the number for the DaysCount?
Click to expand...
In the attached, start date in A2, end date in A3. The table extent should include both dates.
Sam Longstaff said:
Can you explain why there's the additional formula - '=TheSchedule(J2:J367,42,5)?
Click to expand...
It's just trying to be clever; it replaces the multiple formulae in cells AP2:BN2. It's a lambda formula, you can see it in the Name Manager. You don't have to use it.
 

Attachments

  • Chandoo50739On call list workbook v2d.xlsm
    105.5 KB · Views: 14
p45cal said:
In the attached, start date in A2, end date in A3. The table extent should include both dates.

It's just trying to be clever; it replaces the multiple formulae in cells AP2:BN2. It's a lambda formula, you can see it in the Name Manager. You don't have to use it.
Click to expand...

You are VERY clever! I doff my cap to you sir!
Thanks for your input / solution /help, it is greatly appreciated.
 
You must log in or register to reply here.
Back
Top