Hey everyone, I have been reading through tutorials and threads and seen solutions for automatic scheduling, but there are so many conditions I need to consider I'm not sure if what I want to do is possible in VBA. I have attempted to write out how the logic will flow (not in VBA yet, just in trying to understand how to organise the steps) at the bottom but my brain honestly hurts so any help in how to approach this would be appreciated (or whether even excel is suited for this)
Problem Statement:
Use VBA to automatically assign shifts according to a set of conditions to individuals with various constraints.
Background
First, I have a list of staff to whom I need to assign 1 of the following shifts over the course of a 4 (28days) or 6 (42 days) week roster:
M = Morning
A = Afternoon
N = Night (11pm - 12am)
NO = Night Off (12am -8am) (Night shift crosses midnight hence is assigned as 2 consecutive shifts on the roster)
O/X/Leave = Off day/Protected Time day/Annual Leave
Amongst the 40+ staff I have, some cannot work afternoon shifts, or can only work morning shifts, kept in a sheet called Staff (I am not sure if this is the best way to keep this information):
I need a certain number of staff on Mons and Tues, and a different number of staff the rest of the days of the week, kept in a sheet called Shift Req (I have reduced the numbers proportionately for ease of illustration):
Every month I receive from the staff an excel sheet that indicates what shift they would like to work on specific dates. Blank cells means I can assign any shift on that day. I combine these sheets into a master data set to work with, which would look something like:
The order of tasks to perform is as such:
1) Assign N shifts first (as night shifts span 2 days)
2) Assign A shifts next
3) Assign M shifts next
4) Assign O/X/Leave last
Conditions:
1) Each staff should not have more than 4 night shifts in a 4 week cycle (ie, 2 night shifts in a week is fine as long as the final total does not exceed 4)
2) An A shift cannot be followed immediately by a M shift (ie AOM is okay but AMO is not)
3) Each staff should not have more than 5 consecutive shifts (night shift counting as 2 shifts) without a break (O/X/L) - (ie, MAONNoAA is okay but OMANNoAA is not)
The reason why I need to perform the tasks in steps is because I will need to manually re-arrange if the resulting output combines a selection of junior staff only (the staff on the roster are arranged in order of seniority from left to right, and a mix is preferred although not 100% necessary).
Also, different staff have different shift number requirements - ie, some staff may work only 10 out of 28 days and some might work 20 out of 28 days)
Thus, some days will have more staff who need to work than shifts available, and vice versa. I will have to troubleshoot these cases manually by contacting the affected staff.
Attempt at Approach:
I was thinking of using a VBA to evaluate and assign shifts in a week, and then loop that for 4 weeks, but that would be problematic for night shifts that crossed from sunday to monday.
Hence:
To assign night shift on monday first - Check rows 2, 9, 16, 23 - if total number of N shifts in these rows <3, then,
Amongst staff columns who can do night shift (should I reference the Staff capabilities worksheet as an array? Or should I assign the cells in that column a T/F property for each type of shift?)
Look for staff columns with 2 consecutive blank cells, and with the total number of N shifts <4, (can these conditions be written in VBA?)
Of the staff columns that fulfill these 2 criteria, begin assigning the remaining N shifts to the respective staff columns from left to right
Assign N to the first blank in the column and NO to the 2nd blank in the column (Or should I just assign N to the first blank, and manually fill in NO?)
To assign night shift for Tues - Sun - Check every row other than 2, 9, 16, 23, if total number of N shifts < 2, then, repeat the same as above
To assign A shifts on Mon and Tues - Check rows 2-3, 9-10, 16-17, 23-24, if total number of A shifts < 3, then,
Amongst staff columns who can do A shift (same question as above with how to reference)
Begin assigning the remaining A shifts to the eligible staff columns from left to right
Repeat for A shifts on Wed-Sun
Repeat of M shifts
Does this make logical sense and would it be feasible? I understand the theory of If/Then/Else but I don't know how to use VBA to describe things like "within this row, find columns with 2 consecutive blank cells" etc.
Currently I do all these steps manually and use conditional formatting to colour code different shifts and blank cells and count the remaining shifts I need to assign per day. It takes me around a week to finish for 40 odd staff so anything that could help shorten the pain would be a huge help. If automated scheduling like this is not possible, then I will focus more on looking for a way to run a VBA that alerts me when one of the conditions (no M after A, no more than 5 shifts in a row) has been violated.
Thank you so much for reading through!! Any form of advice would be greatly appreciated.
Problem Statement:
Use VBA to automatically assign shifts according to a set of conditions to individuals with various constraints.
Background
First, I have a list of staff to whom I need to assign 1 of the following shifts over the course of a 4 (28days) or 6 (42 days) week roster:
M = Morning
A = Afternoon
N = Night (11pm - 12am)
NO = Night Off (12am -8am) (Night shift crosses midnight hence is assigned as 2 consecutive shifts on the roster)
O/X/Leave = Off day/Protected Time day/Annual Leave
Amongst the 40+ staff I have, some cannot work afternoon shifts, or can only work morning shifts, kept in a sheet called Staff (I am not sure if this is the best way to keep this information):
I need a certain number of staff on Mons and Tues, and a different number of staff the rest of the days of the week, kept in a sheet called Shift Req (I have reduced the numbers proportionately for ease of illustration):
Every month I receive from the staff an excel sheet that indicates what shift they would like to work on specific dates. Blank cells means I can assign any shift on that day. I combine these sheets into a master data set to work with, which would look something like:
The order of tasks to perform is as such:
1) Assign N shifts first (as night shifts span 2 days)
2) Assign A shifts next
3) Assign M shifts next
4) Assign O/X/Leave last
Conditions:
1) Each staff should not have more than 4 night shifts in a 4 week cycle (ie, 2 night shifts in a week is fine as long as the final total does not exceed 4)
2) An A shift cannot be followed immediately by a M shift (ie AOM is okay but AMO is not)
3) Each staff should not have more than 5 consecutive shifts (night shift counting as 2 shifts) without a break (O/X/L) - (ie, MAONNoAA is okay but OMANNoAA is not)
The reason why I need to perform the tasks in steps is because I will need to manually re-arrange if the resulting output combines a selection of junior staff only (the staff on the roster are arranged in order of seniority from left to right, and a mix is preferred although not 100% necessary).
Also, different staff have different shift number requirements - ie, some staff may work only 10 out of 28 days and some might work 20 out of 28 days)
Thus, some days will have more staff who need to work than shifts available, and vice versa. I will have to troubleshoot these cases manually by contacting the affected staff.
Attempt at Approach:
I was thinking of using a VBA to evaluate and assign shifts in a week, and then loop that for 4 weeks, but that would be problematic for night shifts that crossed from sunday to monday.
Hence:
To assign night shift on monday first - Check rows 2, 9, 16, 23 - if total number of N shifts in these rows <3, then,
Amongst staff columns who can do night shift (should I reference the Staff capabilities worksheet as an array? Or should I assign the cells in that column a T/F property for each type of shift?)
Look for staff columns with 2 consecutive blank cells, and with the total number of N shifts <4, (can these conditions be written in VBA?)
Of the staff columns that fulfill these 2 criteria, begin assigning the remaining N shifts to the respective staff columns from left to right
Assign N to the first blank in the column and NO to the 2nd blank in the column (Or should I just assign N to the first blank, and manually fill in NO?)
To assign night shift for Tues - Sun - Check every row other than 2, 9, 16, 23, if total number of N shifts < 2, then, repeat the same as above
To assign A shifts on Mon and Tues - Check rows 2-3, 9-10, 16-17, 23-24, if total number of A shifts < 3, then,
Amongst staff columns who can do A shift (same question as above with how to reference)
Begin assigning the remaining A shifts to the eligible staff columns from left to right
Repeat for A shifts on Wed-Sun
Repeat of M shifts
Does this make logical sense and would it be feasible? I understand the theory of If/Then/Else but I don't know how to use VBA to describe things like "within this row, find columns with 2 consecutive blank cells" etc.
Currently I do all these steps manually and use conditional formatting to colour code different shifts and blank cells and count the remaining shifts I need to assign per day. It takes me around a week to finish for 40 odd staff so anything that could help shorten the pain would be a huge help. If automated scheduling like this is not possible, then I will focus more on looking for a way to run a VBA that alerts me when one of the conditions (no M after A, no more than 5 shifts in a row) has been violated.
Thank you so much for reading through!! Any form of advice would be greatly appreciated.
