4 minute read

Staff Scheduler

Leave it to me to find the coolest side project ever to work on. /s

Recently, I have been working on a very useful (but not sexy) side project for my extracurriculars. I would like to detail my design and thought process in writing, so that it may be of use in the future.

Objective

Create a simple-to-use scheduler for staff rotation, with inputs for individual availability on a single time horizon basis, with optimized backoff so that all individuals have a fair chance to work in the different available positions.

I realize the objective on its own is missing a lot of context, so I will try my best to explain that here.

Background

This program, of sorts, is quite applicable to most scheduling needs that I have seen available on the market. I have n individuals, each with their own availability from work session to work session.

When I say work session, I mean the next schedule-able unit of time. For example, a work session is 1 shift of time, depending on how the situation defines it. If work is occurs every day, each work session would be 1 day, and the next work session is tomorrow (assuming we start from today). If work is only on Saturdays, the work session would be once a week.

Each of the n individuals are capable of performing m tasks. Each task requires special training and minimum requirements to be met before the individual can perform that task. In that case, let us say there are 10 individuals. Only 2 individuals can perform the manager task. That means that, even if those 2 individuals can do all the other tasks, at least one of the two have to be assigned the manager task.

In our simplified case, each individual can only perform a single task during one shift. This assumption assists in reducing the complexity.

Now, to the backoff. At the end of the day, all tasks must be assigned an individual to perform it. However, as a scheduler, I would like to do my best in fairly distributing each task so that one individual is not stuck doing the same role every time. On the flip side, I do not want other individuals to be deprived of the opportunity to try different roles.

In order to cod-ify the backoff, the program must determine some sort of priority function to add uneven weights towards certain individuals performing certain tasks.

If I want individual A to perform task A less often, I would lower their weighting such that it would be maybe 1/2 as likely for that pairing to occur, as opposed to all users who are able to perform task A to have an even probability of getting paired up. The challenge was determining what sort of priority function would fit well.

The current priority function that I have written is based on the last time the individual has performed each task. If individual A performed task A this week, their priority to perform task A next week would be 7 (abs(today - 1 week from now) = 7).

However, if individual B has not performed task A since four weeks ago, their priority value for task A would be 35 (abs(4 weeks ago - 1 week from now) = 35). When the objective function takes in this priority, individual B would be 5x as likely to matched to task A.

That concludes the background information for the problem. That leaves us with …

Design & Implementation

Originally, I wanted to use a more modern tech stack to write this program. However, I was thinking about which tool was right for the job and my mind instantly went to Microsoft Excel. There is a built-in plugin to Excel called Solver, which is fantastic for solving these sorts of optimization problems. It is a very powerful utility and really addresses all of my needs.

After I was able to map the background information into spreadsheets and tables, I was ready to plug and play the Solver utility to perform the Simplex Algorithm on my simple Linear Programming problem. I am not interested in delving into how Solver works in this post, but suffice it to say that Solver can properly optimize my objective function to achieve the most ideal result.

Ideal meaning the solution with the greatest summation of my priority function.

I wrote up a simple example with two individuals and a single role. Once that Solver scenario worked, I continued to add more and more complexity with availability and priority. As everything was working as expected, I wanted to automate everything. With these tools in mind, I set to work with writing a deity-like macro for the ages. Here are the tasks that I came up with in brainstorming:

  • create user
  • delete user
  • output schedule
  • update README
  • Make excel more abstract
  • create macro for new roles
  • create macro to run solver
  • create macro to update user profiles with last run date
  • delete roles
  • check if solver is installed before running overall macro

I am currently in the middle of implementation, but I thought it would be neat to document the design and implementation phase. I have trouble starting and finishing projects, but this structured format of chunking up the problem has helped immensely.

In summary, my thought patterns went something like this:

  1. Define the relationship problem, as clearly as possible
  2. See if there are free alternatives online
  3. Investigate said solutions if applicable, otherwise roll your own
  4. Break down the task into manageable pieces
  5. Prioritize tasks and tackle one at a time
  6. Finish a single task, limit refactoring time
  7. Re-assess priority and begin on next task

I’ll continue to write about this as the program gets closer to completion. Until next time!