Yes, you can build an employee rotation schedule in Excel using a template that combines a shift table with a rotating date formula. The core method uses the MOD and ROW functions to cycle employees through shifts automatically, and Microsoft offers a free built-in shift schedule template in Excel 365. Below is a complete guide to creating, customizing, and troubleshooting your own rotation schedule.
What a Rotation Schedule Actually Needs
A rotation schedule assigns employees to shifts (Day, Night, Off) on a repeating cycle, such as 4 days on, 2 days off, or a weekly pattern. Unlike a fixed schedule, rotation repeats predictably, so you can plan weeks or months ahead. In Excel, the minimal requirements are:
- A list of employees (names or IDs) in rows.
- A list of dates or shift numbers in columns.
- A pattern length (e.g., 7 days) and a starting shift for each employee.
- A formula that returns the correct shift for each date based on the pattern.
The most common pattern is a 4-on/2-off cycle (6-day period) or a 5-on/2-off (7-day period). You can also rotate among three shifts (Morning, Evening, Night) with a fixed number of days per shift.
How to Build a Basic Rotation Template from Scratch
Assume you have employees in cells A2:A10, dates in B1:H1 (e.g., Monday to Sunday), and you want a 4-on/2-off pattern starting with Day shift for everyone. Use this formula in cell B2 and drag it across and down:
=IF(MOD(ROW()-2+COLUMN()-2,6)<4,"Day","Off")
Here, ROW()-2 gives a row offset (0 for first employee), COLUMN()-2 gives a column offset (0 for first date), and MOD(...,6) cycles through 0 to 5. Values 0-3 return "Day", values 4-5 return "Off". To shift the start for different employees, add a starting offset: for employee 2 starting on day 2 of the cycle, use =IF(MOD(ROW()-2+COLUMN()-2+1,6)<4,"Day","Off").
For a three-shift rotation (e.g., 2 days Morning, 2 days Evening, 2 days Night, 2 days Off), use a lookup table. Put the shift names in cells K1:K4 as "Morning","Evening","Night","Off", then use =INDEX($K$1:$K$4, MOD(ROW()-2+COLUMN()-2,8)+1).
Using Excel’s Built-In Shift Schedule Template
Excel 365 and Excel 2021 include a free template. Go to File > New, type “shift schedule” in the search box, and choose “Shift schedule” or “Employee shift schedule”. This template uses a table with columns for employee, shift start/end times, and a weekly grid. It does not auto-rotate; you manually assign shifts each week. For rotation, you would need to modify the template by adding a pattern column and using the MOD formula above. The template is useful for tracking hours but not for automatic rotation.
How to Choose Between a Manual and Formula-Based Template
Decide based on how often your rotation changes:
- Fixed rotation (same pattern every week): use the MOD/INDEX formulas above. They update automatically when you add dates.
- Variable rotation (e.g., holidays, swaps): use a manual grid where you type shifts. Add conditional formatting to highlight conflicts.
- Hybrid: keep a formula-generated base schedule, then override specific cells with manual entries (type over the formula).
For a fixed 7-day pattern, you can also use the WEEKDAY function. For example, if Monday is day 1 and you want Day shift on weekdays: =IF(OR(WEEKDAY(B$1,2)=6,WEEKDAY(B$1,2)=7),"Off","Day").
Common Problems and How to Fix Them
Three issues appear frequently:
- Off-by-one errors: Your MOD formula returns the wrong shift because the starting row/column offset is off. Check that
ROW()-2returns 0 for the first employee. If your data starts at row 3, useROW()-3. - Dates not updating: If you type dates manually, they won’t advance. Use a formula to generate dates: in B1 put a start date, then in C1 put
=B1+1and drag right. Format as dates. - Weekend confusion: If your rotation ignores weekends, use
WEEKDAYto skip Saturday/Sunday. For example, to rotate only on weekdays, use a counter that increments only on weekdays – this requires a helper row with=IF(WEEKDAY(B$1,2)<6,1,0)and a running total.
To avoid errors, always test with a small dataset first. Use Conditional Formatting > Highlight Cells Rules > Equal To to color “Off” cells red, making it easier to spot mistakes.
Comparing Excel Templates to Dedicated Scheduling Software
Excel is free (if you own Office), flexible, and offline. However, it lacks real-time collaboration for multiple managers, automatic conflict detection (e.g., double-booking an employee), and mobile notifications. Dedicated software like Deputy or When I Work offers drag-and-drop scheduling, shift swapping, and time-off requests, but costs per employee per month. For a small team (under 20) with a simple rotation, Excel is sufficient. For larger teams or complex rules (e.g., union constraints, overtime limits), software saves time. Excel can handle up to about 100 employees before formulas become slow to recalculate.
Costs and Licensing Considerations
Microsoft offers Excel as part of Microsoft 365 (subscription, about $10/month for personal, $8.50/user/month for business) or as a one-time purchase (Office Home & Student 2021, about $150). The built-in templates are free within Excel. If you need to share the file with employees who don’t have Excel, you can save it as a PDF or use Excel for the web (free with a Microsoft account) for viewing. There is no extra cost for using formulas or templates. Avoid paid third-party Excel templates unless you need very specific features; most rotation needs are met by the MOD/INDEX approach.
Safety and Accuracy in Scheduling
When creating a rotation schedule, accuracy prevents understaffing and employee fatigue. Always verify the pattern length matches the number of shifts. For example, a 4-on/2-off cycle has 6 days; if you accidentally use 7 in the MOD divisor, the pattern will drift. Double-check that every employee gets the required rest days (e.g., at least 24 hours off after a night shift). For compliance with labor laws, ensure the schedule does not exceed maximum consecutive workdays (often 6-7 depending on jurisdiction). Excel cannot enforce these rules; you must manually review. To catch errors, use a COUNTIF formula to count “Day” shifts per employee per month: =COUNTIF(B2:H2,"Day").
Alternatives to a Template: Google Sheets and Other Tools
Google Sheets offers the same MOD/INDEX functions and is free with a Google account. The main difference is that Google Sheets auto-saves to the cloud and supports simultaneous editing. To use the same formula in Google Sheets, the syntax is identical. For a simpler visual approach, you can use a pivot table to summarize shifts by employee and date. If you need to send the schedule to employees, consider exporting to PDF or using a shared Excel file on OneDrive or SharePoint. For a non-spreadsheet option, a simple calendar app like Google Calendar can be shared, but it lacks the pattern-based auto-fill.
Frequently Asked Questions
How do I make a rotation schedule that repeats every 2 weeks?
Use a 14-day cycle. In the MOD formula, replace the divisor with 14, and define 14 shift codes (e.g., 1-7 Day, 8-14 Off). For example: =IF(MOD(ROW()-2+COLUMN()-2,14)<7,"Week1 Day","Week2 Off").
Can I print a rotation schedule from Excel?
Yes. Go to File > Print, set the print area to your schedule range, and choose “Fit Sheet on One Page” in Page Setup. Use landscape orientation for wide schedules. Ensure column widths are adjusted so dates don’t wrap.
How do I handle employees who request specific days off?
Add a column for “Requested Off” and use conditional formatting to highlight conflicts. For example, if an employee has a request in column J, use a formula in conditional formatting: =AND(B$1=$J2,B2="Day") to turn the cell red. Then manually swap shifts.
What is the best way to track overtime hours in a rotation?
Add a column for “Hours” next to each shift (e.g., Day=8, Night=10). Use a SUMIF formula per employee: =SUMIF(B2:H2,"Day",$B$10:$H$10) where row 10 contains hours per shift. Then compare to a threshold (e.g., 40 hours/week) using an IF formula.
Does Excel have a template for 24/7 shifts?
Yes, search for “24/7 shift schedule” in File > New. It typically uses a 4-team rotating pattern (e.g., 2 days on, 2 off, 3 on, 2 off). The template is manual, but you can convert it to formulas using the MOD approach with a 14-day cycle.
Related
- Excel Attendance Point Tracker Template: Setup & Use
- Excel and Numbers Compatibility: What Works
- Excel Align Cells Evenly: 4 Quick Ways
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Goes Blank and Stops Responding? Fixes to TryIf Excel goes blank and shows Not Responding, wait briefly first, especially if the workbook is large or…
- Excel File Opens to a Blank Gray Screen? FixesIf an Excel file opens to a blank gray screen, first check whether the workbook is hidden or…
- Excel Paste Keyboard Shortcuts: Copy, Paste Special & MoreIn Excel for Microsoft 365 on Windows, press Ctrl+V to paste the copied or cut cells; use Ctrl+C…
- How to Make a Timeline Visual in PowerPoint From ScratchTo make a timeline visual in PowerPoint, choose Insert > SmartArt > Process > Basic Timeline, enter each…
- Excel Heat Maps: How to Create, Format, and Read ThemIn Microsoft Excel for Microsoft 365 on Windows, create a heat map by selecting a numeric range and…
- Share Outlook Calendar URL: Links and AccessTo share an Outlook calendar URL, open Outlook on the web, go to Settings > Calendar > Shared…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.