Shift Roster Template

About this template

When to use it

A month of shift work written one line per person per day, so hours worked, overtime and the night premium all come off the same record. Three sheets — Dashboard, Input data, Usage guide — and only Input data takes typing.

What the dashboard holds

Everything on the Dashboard is a formula. The top block, “Key metrics — summed automatically from the ‘Input data’ sheet”, holds four cells in B7:E7 — Rows entered, Actual work (h) total, Actual work (h) average, Actual work (h) max — and they move the moment you fill column G, exactly as the note in B8 says. Under it, Summary by work pattern (rows 12–16) splits count, total, average and share across the five patterns listed in column B, and Monthly hours by crew (rows 20–23) is hand-typed with a total in B24 and an average in B25.

Filling order

1. Rows 2–21 of Input data take Shift date, Crew / Worker, Work pattern, Start, End, Break (h), Actual work (h), Overtime (h), Night premium (h), Notes in columns A–J.
2. Put the crew and the person in one cell the way the samples do — “Crew A / Kim Seo-yeon” — so a crew reading and a personal reading come off the same column.
3. Keep Work pattern to Day, Evening, Night, Holiday, Day off. Those five words sit in B12:B16 of the Dashboard and are matched as text against column C; a sixth spelling drops the row out of the summary.
4. Start and End are clock times (08:00, 17:00). They are there to be read, not calculated from.
5. Column G must hold plain numbers. Break is already deducted: eight hours on site with an hour of break is Break 1 and Actual work 8.0.
6. Night premium (h) is typed by hand and holds only the hours actually worked between 22:00 and 06:00 — note 8 of the Usage guide keeps it deliberately separate from the work pattern.

How the formulas calculate

• Rows entered =COUNT(‘Input data’!$G$2:$G$21), then SUM, =IFERROR(AVERAGE(‘Input data’!$G$2:$G$21),0) and =IFERROR(MAX(‘Input data’!$G$2:$G$21),0) over the same range; the IFERROR wrapper returns 0 on an empty sheet.
• Each pattern row pairs =COUNTIF(‘Input data’!$C$2:$C$21,$B12) with SUMIF and AVERAGEIF over column G, and a share of =IFERROR(C12/COUNT(‘Input data’!$G$2:$G$21),0) — a share of the row count, not of the hours.
• Overtime is the one formula inside Input data: =ROUND(MAX(0,G2-8),1), so only the part above eight hours is picked up.
• Beneath the crew table, =SUM(C20:C23) and =AVERAGE(C20:C23) return 528 and 132 with the shipped values.
• The ranges stop at row 21, so row 22 onwards is invisible. Past 20 shifts, copy the last row and widen every $21 reference on the Dashboard.

Examples

• Row 2 — 2026-06-01 · Crew A / Kim Seo-yeon · Day · 08:00 · 17:00 · 1 · 8.0 · 0 · Day handover at 08:00.
• Row 5 carries 10.0 actual hours, so its Overtime cell returns 2.
• Row 9 is a Day off and its time cells are left empty on purpose, which is why Rows entered reads 9 across ten sample lines.
• The nine numeric shifts total 74.0 hours, average 8.2 and peak at 10.0.

Common mistakes

• Typing 0 into Actual work (h) on a Day off row, which drags the average down instead of leaving the row out.
• Inventing a sixth work pattern, so those rows never show up in B12:B16.
• Typing over column H and destroying the overtime formula.
• Putting a whole shift into Night premium (h) instead of only the 22:00 to 06:00 part.
• Scheduling the next shift less than 11 hours after the last one ends — note 9 asks for that gap when crews rotate.