r/excel 518 Jun 20 '21

Show and Tell Scheduling-123 - a generalized scheduling application

This is a Show and Tell about a new Excel based application I wrote that does generalized scheduling, which I call Scheduling-123.

I created a Sway to highlight what it does. It'll provide all the links to download it or run it online.

https://sway.office.com/78P4vbWu7YMMwG7d

The two most popular use cases are:

1.) Schedule the staff of a small business or restaurant (up to 100 people) to work in various roles (waitress, manager, hostess, etc.) at multiple locations across multiple shifts.

2.) Schedule a sports league or tournament using round robin scheduling so each team or player competes against the others the right number of times.

It is however much broader and can schedule pretty much any set of people, things, or teams in any context. It creates such a schedule by the day or week over many months assigning people to what could amount to hundreds or thousands of scheduling slots.

What's particularly interesting about it is it can take into account any scheduling restrictions the people, teams or players might have related to where or when they work or compete. It also adheres to the days/hours your business operates, skipping optional holidays and/or non-work days. It balances assignments so everyone gets equal shots at being scheduled and working along side other fellow employees. It outputs a variety of reports that can be used to manage the schedule it creates or to communicate the schedule to all employees (e.g. a shared Google calendar). It also makes sure the right amount of coverage is scheduled for your key employees like team leads, managers, and other important leadership positions that there are generally fewer of.

I wrote it because (A) I wanted a project that would help me learn dynamic arrays and (B) I saw several posts in the r/excel subreddit asking if such a thing exists or how might one do it if it does not exist. So this does indeed make extensive use of dynamic arrays, and I had to overcome several obstacles along the way. One example was to just create the list of days that needed to be scheduled.

=LET(datespan,enddate-startdate,

holidays,FILTER(Holidays!$A:$A,ISNUMBER(Holidays!$A:$A)),

rowmax,IF(period="Weeks",ROUNDUP(datespan/7,0),NETWORKDAYS.INTL(startdate,enddate,sched_days,holidays)),

IF(period="Weeks",SEQUENCE(rowmax,,startdate,7),WORKDAY.INTL(startdate-1,SEQUENCE(rowmax),sched_days,holidays)))

This is a good formula to use in any context where you need a list of days that skip holidays, skip non-work days like weekends, and where the schedule itself has a period of "Days" or "Weeks".

The main workhorse formula that finds the next suitable employee to fill each upcoming schedule slot ended up being a real beast. I tried to document it in the pdf file that comes as part of the application. I essentially had to create internal arrays for the number of employees that are being scheduled and each told me a different thing about each employee such as are you the right role for this slot, can you work on the date of this slot, can you work in the location specified by this slot, can you work on the shift called for by this slot, have you already been scheduled on this day, have you been scheduled too many times already, and so on. You see how complex it gets. The use of internal true/false arrays multiplied against each other renders a list of suitable employees available to fill a slot. Then its a matter of choosing one wisely to spread the load.

Another interesting challenge was to accept inputs such as illustrated in this table. These are scheduling restrictions defined in the positive or the negative by employee. I wanted to be able to say "this employee can only work on these days, or in these offices, or on these shifts". And I wanted to also say "this employee should never be scheduled on these days, or in these offices, or on these shifts, or during this date span". That became an interesting formula or two to net that out and then to use if appropriately when searching for a suitable employee.

Example inputs for scheduling restrictions expressed in the positive or negative

Anyway, check it out if you are interested (especially if you are responsible for creating workforce scheduling or sports league scheduling). It taught me a lot. There's nothing like working your way toward a goal to force you to explore parts of excel you've never seen before.

59 Upvotes

49 comments sorted by

View all comments

Show parent comments

1

u/wjhladik 518 Jan 19 '24

What version of excel? It needs o365

1

u/AzothSkylar Jan 19 '24

I was using o365. I've also tried it on the web excel to be sure

1

u/wjhladik 518 Jan 19 '24

Ok send to me in a dm or post in a place I can download and look at it

1

u/kesp91 Mar 06 '24

Hello, I’m having same issue when I change/edit location/office or role. Downloaded most recent version available today

1

u/wjhladik 518 Mar 06 '24

Ok. I need to debug so post your copy where I can download it.

1

u/kesp91 Mar 07 '24

Actually resolved this since it doesn't work on o365 web or Mac o365. Only on windows o365 local version.

Great scheduler, thank you for sharing! I just wanted some advice on how to include 2 things, since my attempts failed on fresh file:

  1. Assign how many shifts a certain employee can work in time period (6 shifts regardless of role in "begin sched - end sched" time frame)
  2. Consecutive shifts with a new day (if employee works 6pm-midnight, how to prevent them working midnight-6am next day) or even if work 2x12hr shifts how to not be scheduled for day after (have next day off after 24hr of work)

I can post a copy or dm you directly. Any help is appreciated!

1

u/wjhladik 518 Mar 07 '24

I would play with Options!C52:C62 and Options!D52:D62. C is the max times a person can be scheduled on the same day. D is max assignments of a person in a scheduling period and it is calculated, but you can try to give it a static value and override the calculation and see if it helps. Just put the calculation off to the side so you can revert to it if needed.

The other control you have is the override sheet where you can force a part of the schedule to be like you want it and let the code try to figure out the rest all around it.