Articles · HR
How to Create a Shift Schedule in Excel: Steps, Rules and Template
A good shift schedule makes sure enough people are on site at all times, nobody is permanently disadvantaged and legal rest periods are respected. Here is how to build one in five steps – with formulas you can copy straight into Excel.
Step 1: Work out the staffing need per shift
Before any names go into the plan, answer one question: how many people does each shift need? Start from opening hours, order volume or expected guests. This gives you the minimum staffing per weekday and shift.
People needed = expected work hours in the shift ÷ shift length in hours
Warehouse example: on Monday 1,200 lines are picked and one person picks 60 lines per hour. That is 20 work hours. With an 8-hour early shift you need 20 ÷ 8 = 2.5, so 3 people.
| Weekday | Early (6am–2pm) | Late (2pm–10pm) |
|---|---|---|
| Monday | 3 | 2 |
| Tuesday–Thursday | 2 | 2 |
| Friday | 3 | 3 |
| Saturday | 2 | 1 |
Step 2: Choose the right shift model
| Model | Typical use | Watch out for |
|---|---|---|
| Single shift | Office, trades, small shop | Simple – cover holidays and sickness early |
| Two shifts (early/late) | Restaurants, retail, warehouse | Avoid late → early changeovers (rest period) |
| Three shifts (early/late/night) | Production, logistics, care | Rotate forward: early → late → night |
| Part-time and peak shifts | Lunch rush, weekends | Agree minimum length and fixed times |
For rotating systems, rotate forward (clockwise). This automatically leaves longer gaps between shifts and makes it easier for the body to adjust.
Step 3: Build in the legal rules
In Germany the Working Hours Act (Arbeitszeitgesetz, ArbZG) sets the key limits. Other countries have similar rules – check your local law. A plan that breaks them is risky and leads to fatigue and mistakes.
| Rule | What it says (Germany) |
|---|---|
| Maximum working time | 8 hours per working day, up to 10 hours if the average over 6 months does not exceed 8 hours |
| Rest period | at least 11 consecutive hours between two working days (exceptions possible e.g. in hospitality and care) |
| Breaks | 30 minutes for more than 6 hours, 45 minutes for more than 9 hours |
| Sundays and public holidays | generally work-free; exceptions e.g. for hospitality, with a substitute day off |
Common mistake: a late shift until 10pm followed by an early shift at 6am the next day – that is only 8 hours of rest.
Step 4: Build the plan in Excel
- One row per employee, one column per day. Use codes in the cells: E (early), L (late), N (night), V (vacation), S (sick); days off stay empty.
- Below the plan, count staffing per day and shift and compare it with the minimum.
- To the right of each row, calculate the planned hours for the week.
- Use conditional formatting to highlight gaps in red: understaffing, too many hours, rest-period conflicts.
- Publish the plan in good time and log changes with a date.
Count staffing (early shift on Monday, employees in rows 5 to 14):
=COUNTIF(C$5:C$14,"E")
Weekly hours for one person with 8-hour shifts:
=COUNTIF(C5:I5,"E")*8+COUNTIF(C5:I5,"L")*8+COUNTIF(C5:I5,"N")*8
Rest-period warning when a late shift is directly followed by an early shift (Monday in column C, Tuesday in D):
=IF(AND(C5="L",D5="E"),"Check rest period","")
Step 5: Share shifts fairly and communicate
- Rotate weekend and late shifts and keep count over several weeks.
- Collect requests (e.g. days off) by a fixed deadline before building the plan.
- Publish the plan two to four weeks ahead if possible; in Germany, on-call work must be announced at least four days in advance.
- Plan a small stand-by reserve for sickness instead of always calling the same people.
Note: this article is general guidance, not legal advice. Your employment contract, collective agreement and works agreement take precedence.
Free Excel template
The Shift & Leave Planner already includes a weekly roster with minimum staffing per weekday, hours and overtime per employee, staff costs and a leave account – for up to 25 employees, no macros.