A rotating shift schedule template is a repeating sequence of Day, Night and Off shifts, plus one anchor date that fixes which cycle day falls on a known date. Copy the sequence for your rota, set the anchor, and a spreadsheet can fill the calendar for as long as the pattern holds. Check the first two weeks against your published rota before you rely on it.
A rotating shift template usually breaks at the start date. The sequence can be copied perfectly, yet if the first date lands on the wrong cycle day, every shift is one day out. This guide gives you sequences you can copy, a sheet built from working formulas, and the signs that a spreadsheet no longer keeps up with your rota.
What a rotating shift schedule template needs
Three inputs drive every rotating template: the complete repeating sequence, an anchor date and, if you want hours, working times for each shift. The sequence is the full cycle written as D for Day, N for Night and O for Off. The anchor date says which cycle day falls on a date you can check against your rota.
Keep exceptions out of the sequence. Swaps, leave and overtime change one date, so record them in their own column. The base cycle then stays where it is for every future week.
| Input | What to enter | How to check it |
|---|---|---|
| Repeating sequence | Every D, N and O in one full repeat | Count the days until the first cycle day comes round again |
| Anchor date | The calendar date of cycle day 1 | Match it to a shift you know you worked |
| Working times | Start and end time for Day and for Night | Compare the hours with one published shift |
| Exceptions | Swaps, leave and overtime, one per date | Confirm they leave the base sequence unchanged |
Which rotation patterns can you copy?
The table lists sequences from the RotaFlow preset library. Each row repeats after the cycle length shown. Names vary between employers, so compare the first two weeks with your published rota before you copy a row.
The DuPont row shows one crew's view of a 28-day, 12-hour rotation with four crews. Other crews start at other points in the same cycle. Panama is the rotating 2-2-3, where the day and night teams swap over a full 28 days. Pitman is the fixed form of the 14-day work and rest rhythm.
| Pattern | Cycle length | Copy this sequence |
|---|---|---|
| 4 on / 4 off, day shifts | 8 days | DDDD OOOO |
| 3 on / 3 off, day shifts | 6 days | DDD OOO |
| 4 on / 2 off, day shifts | 6 days | DDDD OO |
| Pitman 2-2-3, day shifts | 14 days | DD OO DDD OO DD OOO |
| Pitman 2-2-3, night shifts | 14 days | NN OO NNN OO NN OOO |
| Panama 2-2-3, days then nights | 28 days | DD OO DDD OO DD OOO NN OO NNN OO NN OOO |
| DuPont, one crew | 28 days | NNNN OOO DDD O NNN OOO DDDD OOOOOOO |
| 4 on / 4 off, day and night blocks | 16 days | DDDD OOOO NNNN OOOO |
DuPont structure: 28 days, 12-hour shifts, four crews ↗
2-2-3 and Panama: the 14-day rhythm and the 28-day full rotation ↗
How do you build a shift schedule template in Excel or Google Sheets?
Use three sheets: Pattern, Settings and Calendar. Excel and Google Sheets use the same functions here, so the formulas work in either program.
Step 1. On Pattern, type the sequence down column B, one code per row. Pitman day shifts need 14 rows and Panama needs 28. Start at B2, so a 14-day cycle ends at B15.
Step 2. On Settings, type the anchor date in B1. That is the calendar date of cycle day 1.
Step 3. On Calendar, enter the first date in A2 and fill down. In B2, enter =INDEX(Pattern!$B$2:$B$15,MOD(A2-Settings!$B$1,ROWS(Pattern!$B$2:$B$15))+1). Dates before the anchor work too, because MOD returns a positive result for a negative offset in both programs.
Step 4. Column C is for exceptions. Type the shift you actually work, D, N or O, only where it differs from the base. In D2, enter =IF(C2<>"",C2,B2) so the effective shift follows any exception.
Step 5. Add a Shifts sheet with D in A2, its start time in B2 and its end time in C2. Put N in A3 with its own start and end times. In E2, enter =IFERROR(MOD(VLOOKUP(D2,Shifts!$A$2:$C$3,3,FALSE)-VLOOKUP(D2,Shifts!$A$2:$C$3,2,FALSE),1)*24,0) and fill down. MOD handles a shift that ends after midnight, so a 19:00 to 07:00 night shows 12. Off rows show 0.
Step 6. Put =SUM(E2:E8) under each seven-day block to total the week. Then test three landmarks: a known Day, a known Night, and the first day back after a rest block. If every date is displaced by the same number of days, move the anchor. Leave the sequence alone.
When does a spreadsheet stop being enough?
A spreadsheet is enough while one person keeps one rota and checks it a few times a week. It starts to strain when the same rota must be read on several devices, changed by swaps without breaking the base pattern, or totalled for pay and overtime over more than one period.
The table compares common jobs with what RotaFlow offers. Free and Pro limits follow the app's plan comparison.
| Job | Spreadsheet | RotaFlow |
|---|---|---|
| Next shift at a glance | Open the file and scroll to the date | Today shows your current shift and the next change, and the small Home Screen widget is free. Medium, large and Lock Screen widgets need Pro. |
| Reminders | A phone reminder set by hand | The free plan offers reminder times at 00:00, 08:00 and 16:00. 15-minute precision needs Pro. |
| Swaps and special days | Overwrite cells and check the formulas | Up to 5 upcoming special-day spans, and swap edits up to 30 days ahead, are free. Pro extends both limits. |
| Overtime totals | Extra formulas for each period | Week summaries are free. Month and year summaries need Pro. |
| Pay estimates | Your own rate formulas | Basic working times and unpaid breaks are free. Pay-period forecasts and advanced pay rules need Pro. |
| Sharing | Export a PDF or share a file | Image and iCalendar export cover 7 days free. Pro adds 14 and 30 day ranges and more customization. An .ics file is a snapshot and does not update on its own. |
| Two or more rotas | One tab for each person | Two rotas over 30 days are free. Up to four rotas over 180 days need Pro. |
| Trades between colleagues | Email and a shared sheet | Joining one group, with limited history, is free. Creating a group and posting trades need Pro. |
Where RotaFlow helps, and where it stops
RotaFlow is a personal planner and calendar. It repeats your Day, Night and Off pattern from an anchor date, keeps exceptions on top of that pattern, and shows the result on iPhone, iPad, widgets and Apple Watch. Core rota planning does not need an account. Optional iCloud sync uses your Apple setup. Pro is available as a monthly, yearly or lifetime purchase through the App Store.
It does not approve leave or replace the scheduling system your employer runs. Treat the published rota as the source of truth, and check the first two weeks in the app against it before you rely on the calendar.
Feature FAQ
Is a rotating shift schedule template free?
Yes. Building the sheet in this guide costs nothing. RotaFlow is free to download and includes core rota planning with the repeating Day, Night and Off pattern. Pro extends planning, widgets and export options.
Does a 12 hour shift schedule template need different formulas?
No. Keep the cycle as Day, Night and Off, then add a start and end time for each shift code. The hours formula handles a night shift that finishes the next morning. RotaFlow also stores Day and Night start and end times, including a shift that ends the next day.
How do I record a shift swap without breaking the template?
Put the shift you actually work in an exception column and let the effective shift follow it. The base cycle never changes, so the weeks after the swap keep their place.
Why is every shift in my template one day out?
The anchor date sits on the wrong cycle day. Check a known workday, the first day of a rest block and the first day back at work. Move the anchor by the number of days out, and leave the sequence alone.
Can I import a spreadsheet into RotaFlow?
RotaFlow does not import Excel or Google Sheets files. You choose a preset or enter a Day, Night and Off sequence with an anchor date. The app can create a separate schedule from the supported all-day events in an .ics file.