Which shift patterns does the workbook include?

Four four-crew 12-hour patterns: DuPont (28-day cycle with a 7-day break), 2-2-3 or Panama (28 days, never more than 3 shifts in a row, every other weekend off), 4-on-4-off (16 days) and 3-on-3-off (12 days). Each is stored as day, night and off codes per cycle day on Setup with its crew offset, the cycle divided by four. You can overwrite one with your own pattern; the Check column tells you if a day ends up with no crew or two crews on a shift.

How is shift coverage calculated?

People on shift divided by people required on that shift. If crew C has 11 people, 1 is on leave and the shift needs 11, coverage is 10 / 11 = 90.9%, which is Below target against a 100% target and a gap of 9.1 percentage points, the same answer as the Shift Coverage calculator for 10 available and 11 required. The rota shows the lower of the day and night shift for each date.

Why does every pattern average 42 hours a week?

Four crews share 168 hours a week of cover, so each crew averages 168 / 4 = 42 hours over a whole cycle. Over the example's 12 weeks on 2-2-3 that is 42 shifts of 12 hours, 504 hours per person. Against a 40-hour contract that is 2.0 hours a week above contract; whether that is paid as overtime or built into the salary depends on your agreement.

How do I plan leave with it?

Type the number of people absent on the day or night shift for each date. People on shift drop, and if they fall below the required number the Status column says Below target and the day counts in Shifts below required. In the example, crew C has 11 people for 11 positions, so any leave on a crew C shift shows as a gap that needs cover from overtime or another crew.

Does it check working-time rules?

No. Limits on consecutive nights, weekly hours and rest between shifts differ by country and agreement, for example the EU Working Time Directive and national laws. The pattern notes on Setup state each pattern's longest run (4 shifts in a row on DuPont and 4-on-4-off, 3 on 2-2-3 and 3-on-3-off) so you can check them against your rules.

Can I use it for 8-hour shifts or five crews?

Not as built: the Rota has two shifts a day and four crew columns, which covers four-crew 12-hour patterns. Eight-hour patterns with three shifts a day, or five-crew patterns, need another code table and more crew columns.

Safety and People

4-Crew Shift Rotation Schedule Excel Template

Choose a four-crew 12-hour rotation pattern and get the day-by-day rota: which crew is on days and nights, how many people each shift will have once leave is booked, the shifts below your minimum, and each crew's hours, weekends and holidays.

Overview

This shift rotation schedule is a working Excel workbook for operations managers, shift supervisors and HR teams in plants that run around the clock with four crews on 12-hour shifts. It ships with a 12-week example rota for a fictional polymer compounding plant on the 2-2-3 (Panama) pattern, with leave booked and two December holidays, so the rota, dashboard and charts are populated the moment you open it.

Every four-crew 12-hour pattern works the same way: each day one crew works days, one works nights and two are off, and every crew follows the same sequence shifted by a fixed number of days. The workbook holds four widely used patterns as codes per cycle day. Pick one on Setup and each crew's code for every date follows from the cycle day, so you can switch from 2-2-3 to DuPont and see the whole rota change. The Check column confirms that exactly one crew is on each shift every day, which also catches a mistyped custom pattern.

People on shift are the crew's headcount minus the people you type as absent, and coverage is people on shift divided by the people required, the same ratio as the Shift Coverage calculator on this site; the rota keeps the lower of the day's two shifts. The dashboard then shows the shifts that drop below the minimum, the weekend and holiday shifts each crew works, and average weekly hours per person: 42 hours for any four-crew 24/7 pattern over whole cycles, which against a 40-hour contract is 2 hours a week above contract.

What this template includes

Four standard four-crew 12-hour patterns as day, night and off codes per cycle day: DuPont, 2-2-3 (Panama), 4-on-4-off and 3-on-3-off, with cycle length and crew offset; choose one on Setup or overwrite one with your own
Rota table with one row per date: weekday, holiday flag, cycle day, each crew's code (colour-coded), the crew on days and the crew on nights
Planned absence per shift, people on shift against the required minimum, the day's lowest coverage and a status word
Check column that proves exactly one crew is on days and one on nights every day, and flags absences larger than the crew and duplicate dates
By-crew summary: day and night shifts, hours per person, average hours per week, hours above contract, weekend shifts and holiday shifts
Dashboard with four KPI tiles, people on shift by day against the minimum, weekend and holiday shifts by crew, and shifts below required by week
Method sheet with every formula, and a Verify sheet that re-checks the formulas against an independent reference, including the Shift Coverage calculator (Self-check: PASS)

Suggested use case

Use it to choose and publish a 24/7 rota, to see before you approve leave whether a shift drops below its minimum, to check a pattern is fair on weekends and holidays, and to explain average hours and hours above contract to crews and HR.

How to use it

On Setup, choose the rotation pattern, the date crew A starts cycle day 1, the people required on every shift, the coverage target and the contract hours per week.
Enter your four crews with their supervisor and headcount.
On Rota, set the dates you want to plan (the example runs 12 weeks), mark holidays with Yes, and type the people on leave, training or sick for each day and night shift.
Read the Check column, then the Status column: Below target marks a day when a shift is short of people.
Open the Dashboard for the lowest coverage, the shifts below required, hours per crew and the weekend and holiday split.

Frequently Asked Questions

Which shift patterns does the workbook include?: Four four-crew 12-hour patterns: DuPont (28-day cycle with a 7-day break), 2-2-3 or Panama (28 days, never more than 3 shifts in a row, every other weekend off), 4-on-4-off (16 days) and 3-on-3-off (12 days). Each is stored as day, night and off codes per cycle day on Setup with its crew offset, the cycle divided by four. You can overwrite one with your own pattern; the Check column tells you if a day ends up with no crew or two crews on a shift.
How is shift coverage calculated?: People on shift divided by people required on that shift. If crew C has 11 people, 1 is on leave and the shift needs 11, coverage is 10 / 11 = 90.9%, which is Below target against a 100% target and a gap of 9.1 percentage points, the same answer as the Shift Coverage calculator for 10 available and 11 required. The rota shows the lower of the day and night shift for each date.
Why does every pattern average 42 hours a week?: Four crews share 168 hours a week of cover, so each crew averages 168 / 4 = 42 hours over a whole cycle. Over the example's 12 weeks on 2-2-3 that is 42 shifts of 12 hours, 504 hours per person. Against a 40-hour contract that is 2.0 hours a week above contract; whether that is paid as overtime or built into the salary depends on your agreement.
How do I plan leave with it?: Type the number of people absent on the day or night shift for each date. People on shift drop, and if they fall below the required number the Status column says Below target and the day counts in Shifts below required. In the example, crew C has 11 people for 11 positions, so any leave on a crew C shift shows as a gap that needs cover from overtime or another crew.
Does it check working-time rules?: No. Limits on consecutive nights, weekly hours and rest between shifts differ by country and agreement, for example the EU Working Time Directive and national laws. The pattern notes on Setup state each pattern's longest run (4 shifts in a row on DuPont and 4-on-4-off, 3 on 2-2-3 and 3-on-3-off) so you can check them against your rules.
Can I use it for 8-hour shifts or five crews?: Not as built: the Rota has two shifts a day and four crew columns, which covers four-crew 12-hour patterns. Eight-hour patterns with three shifts a day, or five-crew patterns, need another code table and more crew columns.