Generating a 4 On/4 Off Work Schedule

Danni's company uses a rotating 4 on/4 off work schedule, using 12-hour shifts. (Employees work 7:00 am to 7:00 pm for 4 days, then are off for 4 days, then work 7:00 pm to 7:00 am for 4 days, then are off for 4 days, etc.) The company never closes, even for holidays, but Danni would like to use Excel to forecast six-month work schedules for individual workers. It isn't readily apparent to Danni how the various workday-related functions in Excel could be used for this.

There are two templates that Microsoft makes available—Shift Work Calendar and Shift Work Calendar Year at a Glance—that may do the trick for Danni's needs. But, it wouldn't be too hard to create your own workbook to generate the schedule. The key to this is remembering that dates are nothing but numbers, so you can do all the necessary calculations without the need of resorting to any of Excel's built-in workday functions.

Let's take a look at a simple example of what I mean. You could put a date such as "1/1/23 7:00 am" into cell A1, and then format the cell so that it shows the date and time. (See Figure 1.)

Figure 1. A date added and formatted.

Now, in cell A2, enter the formula =A1+1 and format the cell the same was as you formatted cell A1. (See Figure 2.)

Figure 2. Incrementing the date.

Now you can use the same formula in cells A3 and A4, and you end up with a four-day morning schedule. Now, in cell A5, you can use the formula =A1+8.5 and do the formatting. (See Figure 3.)

Figure 3. Starting a new cycle.

Notice that the date shown now shows 7:00 PM, just as you would want for the next shift cycle. This is because you added a half day (.5) to the date, which moved it by 12 hours.

You could continue making formula adjustments in this manner in order to set up your schedule, but remember that the tricky part is to make sure you add the half day when you should and subtract it when you should, in order to do the AM/PM shift change properly.

Another way to approach the issue is to, in column A, put all the dates in your six-month period. Then, in column B, you could use a formula like this:

=CHOOSE(MOD(A1,16)+1,"7:00 to 19:00","7:00 to 19:00","7:00 to 19:00","Off","Off","Off","Off","19:00 to 7:00","19:00 to 7:00","19:00 to 7:00","19:00 to 7:00","Off","Off","Off","Off","7:00 to 19:00")

Copy the formula down for each date in column A, and you can see the schedule pattern. All it does is to divide the date—again, remember that dates are nothing but numbers—by 16 and then return one of three values that indicate when (or if) the person is working that day. You would need to check to make sure that the value returned matches your company's records, and if it doesn't, adjust the CHOOSE function appropriately.

In fact, this approach (using CHOOSE) doesn't even require that the dates be in sequential order. You can use any date with the formula, and it will do the calculation just fine and return whether (or if) that date is a workday.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (10759) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, 2021, and Excel in Microsoft 365.