VBA to autofill a work schedule

Dustinkli

New Member
Joined
Mar 26, 2019
Messages
48
Office Version
  1. 365
  2. 2011
Platform
  1. Windows
I am responsible for making work schedules at my job and I do them in a way where different people are assigned different tasks on different days of the week. Also, each weekend 2 people work and they get 2 days off during the week as well.

I would like to get any ideas on a way I can make a VBA that automatically fills out the blank days on the schedule while meeting certain rules (i.e. No one gets assigned the same thing on the same day and ,IF possible, no one gets assigned the task twice in a week).

The monthly schedule is formatted like this:

MondayTuesdayWednesdayThursdayFridaySaturdaySundayMondayTuesdayWednesday
BobTask ATask CTask BOffOffTask ATask C
AmberTask BTask ATask COffOffTask BTask A
JasonTask CTask BTask ATask ATask BOffOffTask C
AmeliaOffTask CTask BTask ATask BTask AOffOffTask B
BillOffOffTask CTask BTask AOffOffTask CTask BTask A

What I'd like to do is be able to set up the schedule with requested days off first then run the VBA and it automatically fills out the rest of the schedule with scheduled tasks per the specified rules leaving what is already there alone.

I'm not sure where to begin with this so I wanted to get some input and tips on where I should start or if anyone has encountered something similar to this already written which I can modify to suit my purposes.
 

StevenP138

New Member
Joined
Nov 3, 2020
Messages
2
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
I have a similar request. This gets me almost there. But how do you set it up where someone cannot do the job?

Example: Bill is not trained on how to do Job B.
 

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

wheeeler

New Member
Joined
Nov 10, 2020
Messages
1
Office Version
  1. 2016
  2. 2010
Platform
  1. Windows
  2. MacOS
I have a similar request. This gets me almost there. But how do you set it up where someone cannot do the job?

Example: Bill is not trained on how to do Job B.
I solved a similar problem with a clunky string-based FIFO approach. 22 people, 10 benches, 2 sets of hours, 24-hour operations

Background:
I supervise and schedule a team of night-shift scientists, technicians, and students that work up to 10 benches at any given time. Our workload varies by time and day, and during our slow hours we can drop down to 4-5 people without issue. My team enjoys a set schedule (same days of the week), with most of them working three 12-hour days and a few working four 10s. We divided the lab into two main zones comprised of five benches each. We rotate bench assignments halfway through the night to reduce fatigue, conflict, and skill atrophy. Whenever possible, personnel will start their shift in one zone and finish in another. Each zone has one princess bench and one bastard bench. My goal is to assign no more than one bastard bench per week and evenly distribute the princess rotations. My students are restricted to three benches in one zone, I have a subject-matter expert who works all five benches in the other zone, and one who works a total of four benches across both zones.​

Organize data:
  • Abbreviate bench assignments to single-character codes: LVAWFSCUHB
  • Abbreviate days of week to single-character codes: XMTWHFS
  • Construct a table:
    • NAME | DAYS | BENCHES
    • JOHN | MTW | SCUH
    • DAVE | MTH | ULVCSAHW
    • JILL | XS | SVLCVB
      • the bench letters are in chronological order (most recent assignment last)
  • We will add columns to the right of these, 2 for each day of the schedule period
Loop through schedule period:
  • Add 2 columns to the right side of the table, one for each assignment of the day
  • Select an employee to assign (I haven't built the logic, so I randomize the list at the beginning of each round)
  • If current working day is not present in the current employee's DAYS column, go to next employee
  • Loop through BENCHES:
    • If first letter in BENCHES list is a bastard bench and current assignment is a bastard bench, go to next letter
    • If next letter in BENCHES is in the same zone as current bench, go to next letter
    • If next letter in BENCHES is available, assign it and send the letter to the end of the person's BENCHES string
It then copies the whole month's assignments to a worksheet with some smart color coding to help spot errors. There isn't a lot of real-time problem solving going on here, so it leaves some gaping holes in the schedule. These turn red for me, as do the duplicate assignments on the same shift. I'll repeat this a couple of times, pick the most complete schedule, then go through and check it by hand.

Thanks for coming to my TED talk lol
 

Watch MrExcel Video

Forum statistics

Threads
1,128,101
Messages
5,628,696
Members
416,333
Latest member
Time2Learn

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top