VBA to autofill a work schedule

[Dustinkli]

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:

	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday	Sunday	Monday	Tuesday	Wednesday
Bob	Task A			Task C	Task B	Off	Off	Task A	Task C
Amber	Task B	Task A			Task C	Off	Off	Task B	Task A
Jason	Task C	Task B	Task A			Task A	Task B	Off	Off	Task C
Amelia	Off	Task C	Task B	Task A		Task B	Task A	Off	Off	Task B
Bill	Off	Off	Task C	Task B	Task A	Off	Off	Task C	Task B	Task 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]

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.

 

[wheeeler]

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