You’ve created a week’s schedule for your restaurant staff by day and shift (AM or PM). Next, you want a way fool-proof method to check if anyone is double-booked on any given day, and if so, who. Surely you can manually double-check and triple-check to make sure the schedule is exactly as planned but there are better ways, multiple ways, to verify it that’s accurate and scalable. In this post, I’ll discuss some of the methods using Excel.
Let’s start with an example. You have the following draft schedule.
Your task first is to find if anyone is double-booked on any day and if so find the information so you can make appropriate adjustments. The first method is to use conditional formatting. It’s simple but it relies on the UI meaning when UI label or positions change, you’ll have to readjust the steps. However, it requires very little additional work beyond that. Let’s look at the steps needed.
1 Select the entire range, and from Home menu: Conditional Formatting->New Rule…and select ‘Use a formula to determine which cells…’ There enter this formula:=COUNTIFS(Staff_column_data_range, Staff_column_1stcell_ref, Day_column_data_range, Day_column_1stcell) > 1e.g. =COUNTIFS(B33:B52, B33, D33:D52, D33)>1 where B column holds staff names, D column holds Days assigned to each of them. Data at row 33 through 52.
Click Format on the dlg, then Fill tab, and select a color to highlight the double-booked items.
OK, OK to close the dlg.
The staff names who are double booked for the same Day will be highlighted in the color you chose.
2 To make it easier to read, select the Header cells, then Home: Sort & Filter-> Filter and choose ‘Filter by color’ and the color you used to highlight will show there as a choice, select it.
Now it will show all conflicts together at the top.
We see that Alina and Evan are highlighted (when we’re checking for Day conflict only: B and D columns in the formula).
That means Alina is scheduled on Saturday more than once for the week and Evan is scheduled for Wednesday more than once for the week. However, you’ll notice that while Alina was scheduled on Saturday more than once and on the same shift (PM), Evan is actually scheduled for Wednesday twice but for different shifts (once for AM, another for PM)…which may be what you wanted. Remember, the solution so far will show the staff scheduling conflicts by the Day.
In order to look for conflicts when both Day and Shift are the same, we’ll need to modify the formula just a bit. So, you want to be more specific, that is, show conflicts only if BOTH the Day AND Shift are same for a person. To accomplish this, change the COUNTIFS() formula to =COUNTIFS(B33:B52, B33, C33:C52,C33, D33:D52, D33)>1
After you confirm all the dialogs and close them, you’ll see any person’s name highlighted in the range only if both Day and Shift are the same for the person. After modifying the formula, we see only Alina is highlighted:
That’s because only she is booked on the same Day AND the Shift for the week. Now, you can easily find such errors and adjust the schedule.
Now that the formula is set, if you change the values for either Shift or Day in the dataset, we’ll see the highlights go away dynamically (or reappear if there are conflicts again).
These solutions so far have been somewhat UI-based solutions combined with conditional formulas. Next, let’s look at my preferred method that does not use conditional formatting, instead uses FILTER to extract the different types of conflicts (if any).
This method requires a little more upfront work but is very robust and easier to scale. Besides, Conditional Formatting still requires eye-balling the results to find the highlighted cells to see the conflicts. If you have several hundred rows of data (maybe a schedule not for one restaurant but your huge factory!), then looking for the highlighted cells can be error-prone and strenous. For that, we want to extract exactly what we want in a new space, the way we want!
We start with creating a helper column for each type of conflict. Since we’re considering 2 types of conflicts: 1) Someone is double-booked (or triple- or more) on the same Day, or 2) Someone is double-booked (or triple- or more) on the same Day and the same Shift. We’ll therefore need two helper columns where they hold the number of occurences for each type of conflict. Then we’ll use filter to extract values where the count is greater than 1 from each to find conflicts of each type. Voila! Here are the details…
The Helper1(staff_day) and Helper2(staff_day_shift) are the 2 columns you’ll have to create. The first one is a simple concatenation of staff name (column A) and the Day assigned to the name (column D). And for readability, I join the name and day values with an underline. You can use “&” or CONCAT function to do this. Then just drag the formula down for all rows in range. Repeat the same process for Helper2(staff_day_shift) except here, you want to join name, day, and shift values for each row.
Next, we simply count the number of occurences for Helper1 and put the count in a new column called Count_Helper1. The formula would be: =COUNTIF($D$80:$D$99, D80) and drag it down to all rows.
Do the same for Helper2 column and put the count in a new column Count_Helper2. The formula would be: =COUNTIF($F$80:$F$99, F80)
Everyone that’s double-booked on a day, will show 2 in the Count_Helper1 column (and if triple-booked, it’d show 3 and so on). And everyone that’s double-booked on a day AND the same shift will show 2 in the Count_Helper1 column (and if triple-booked, it’d show 3 and so on). Great! The hardest part is already done; at this point you can simply a Filter (from Home menu->Sort & Filter group) to the range and simply select to filter by the value you want (e.g. 2) from the filter drop-down which gets auto-populated by Excel as in a pivot table.
Or, as I mentioned earlier you can also extract only what you want from this solution range using FITLER function described below.
To extract all conflicts by the Day:
Enter this formula in a blank cell: =CHOOSECOLS(FILTER(A80:E99,E80:E99>1),{1,2,3,5}) and bam! Look at the extracted values below:
It shows Alina and Evan were booked twice each on the same day…Alina twice on Saturday, and Evan twice on Wednesday. This is showing the conflict by Day only since we’re using Count_Helper1 column (E) in the formula. Perfect!
The use of CHOOSECOLS function is optional but I recommend it because it allows you to choose which columns to show in the results returned by FILTER. Otherwise, it’d return all columns, which can clutter up your report with unncessary details.
To extract all conflicts by the Day AND Shift:
Enter this formula in a blank cell: =CHOOSECOLS(FILTER(A80:G99,G80:G99>1),{1,2,3,5}) and bam! Look at the extracted values below:
It shows only Alina was booked twice each on the same day (Saturday) and the shift (PM). Here we’re using Count_Helper2 column (G) in the formula. How cool is that!
You can try with different datasets by creating double, triple, quadruple conflicts and this method will work without ANY more modifications or additional formulas. That’s the beauty of this approach and the reason why I prefer this solution over the more commonly talked about Conditional Formatting method.
I hope this was informational for you. For more helpful topics on Excel, Python and more, feel free to explore my blog site (you can search by words, tags, etc.). If you find the posts helpful, please consider a small donation (one-time or recurring is up to you) by clicking here (securely transacted via PayPal™).
