Work schedule template for Excel: a weekly rota you can build yourself

In short: one row per person, one column per day, a shift code from a drop-down in each cell. A second sheet holds the start, end and break of every shift. Weekly hours come from =SUMPRODUCT(COUNTIF(C6:I6,ShiftCodes)*ShiftHours), staff per shift from COUNTIF, and conditional formatting flags any shift that starts less than 11 hours after the previous one ended.

Before you start: calculating with times, COUNTIF and mixed references. SUMPRODUCT is explained in the exercise.

Try it now

Before the schedule can add up hours, each shift needs its length. A night shift from 22:00 to 6:00 ends before it starts, so the formula has to handle midnight.

Task In E2, calculate the hours worked in the shift as a decimal number: end minus start, minus the break in minutes. The formula is filled down to E5 and must also work for the night shift across midnight.

	A	B	C	D	E
1	Shift	Start	End	Break (min)	Hours
2	Early	6:00	14:00	30
3	Late	14:00	22:00	30
4	Night	22:00	6:00	30
5	Day	8:00	16:30	30

Download the work schedule template

The file holds one week for up to twelve people, colored shift codes from a drop-down, planned hours and the difference to contract hours, staff per shift against a minimum, a warning for short rest, a Shifts sheet with times and an instructions sheet.

How the weekly schedule is laid out

Cell or column	Contents	Input or formula
B2	Any date in the week	Input, such as 4 Jan 2027
B3	Weeks start on	Sunday or Monday
C5 to I5	The seven days	=B2-WEEKDAY(B2,IF($B$3="Monday",2,1))+1, then +1 each
E2	Week number	=IF($B$3="Monday",ISOWEEKNUM(C5),WEEKNUM(C5,1))
A, B	Name, contract hours per week	Inputs
C to I	Shift per day	Code from the drop-down, empty = day off
J	Planned hours	=SUMPRODUCT(COUNTIF(C6:I6,ShiftCodes)*ShiftHours)
K	Difference	=J6-B6

The week start is your choice. In the US a week usually runs Sunday to Saturday and WEEKNUM counts the week that contains January 1 as week 1. In the UK weeks run Monday to Sunday and ISOWEEKNUM gives the ISO week. For US payroll, the Department of Labor describes a workweek as a fixed, recurring period of 168 hours that may begin on any day, so set B3 to match the workweek your employer uses.

The Shifts sheet lists each code with start, end and break in minutes, and column F works out the hours with =MOD(D5-C5,1)*24-E5/60. ShiftCodes and ShiftHours are names for columns A and F of that sheet, so every shift time lives in one place: move the early shift to 5:30 and every weekly total follows. The drop-down reads the same list, as shown in the lesson on drop-down lists.

Weekly hours with SUMPRODUCT and COUNTIF

Each person works a mix of shifts. You could count every code on its own and multiply by its hours, but COUNTIF can count all codes at once: with a range as the criteria it returns one count per code, and SUMPRODUCT multiplies those counts by the hours and adds them up.

Task In I2, calculate the planned hours for the week: for each code in K2:K5, count how often it appears in the row and multiply by that shift's hours in L2:L5. The formula is filled down to I5.

	A	B	C	D	E	F	G	H	I	J	K	L
1	Name	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Hours		Code	Hours
2	Olivia	E	E	E	E	E					E	7.5
3	James	L	L	L			L	L			L	7.5
4	Emma	N	N	N	N						N	7.5
5	Noah	D	D	E							D	8

This exercise is part of Premium.

Premium unlocks every exercise in every lesson, unlimited practice with fresh tasks and the final test with certificate.

See Premium Already Premium? Log in

The first exercise in this lesson is free.

Vacation (V) and sick (S) have no times on the Shifts sheet, so they count as 0 hours. If paid time off should count towards the week, type the hours straight into column F for V. For the hours people actually worked, use the timesheet template.

Shift schedule: staff per shift

Below the rota, a small table shows how many people work each shift on each day. One formula fills the whole table when the references are mixed correctly: the rows of the staff list stay fixed, the day's column moves, and the code always sits in column A. The lesson on absolute references explains why.

Task In B10, count how many people work the shift in A10 on Monday. The formula is filled right to Sunday and down for all three shifts (B10:H12).

	A	B	C	D	E	F	G	H
1	Name	Mon	Tue	Wed	Thu	Fri	Sat	Sun
2	Olivia	E	E	E	E	E
3	James	L	L	L	L	L
4	Emma	N	N	N	N
5	Noah			L	E	E	E	E
6	Ava	D	D				N	N
7	Liam		N			N	L	L
8
9	Shift	Mon	Tue	Wed	Thu	Fri	Sat	Sun
10	E
11	L
12	N

This exercise is part of Premium.

Premium unlocks every exercise in every lesson, unlimited practice with fresh tasks and the final test with certificate.

See Premium Already Premium? Log in

The first exercise in this lesson is free.

Column B holds the minimum staff per shift. A conditional formatting rule, =C20<$B20, turns any day red where too few people are planned. The codes in the rota have their own colors too, one rule per code such as =C6="N".

Rota or scheduling template for teams without shifts

Not every team works in shifts. For an office, a shop or a practice, a simple rota with a day shift, a late shift and days off is often enough. Change only the Shifts sheet: D for 8:00 to 16:30, maybe H for a half day, and delete the rest. The formulas in the schedule stay the same. Repeating patterns are quick to copy: select a row, copy it and paste it into next week's sheet. To plan vacations across the whole year, the PTO tracker is the better tool, and the attendance tracker records who actually turned up.

Working time rules in the UK and the US

In the UK most workers have the right to an uninterrupted 20-minute rest break when they work more than 6 hours a day, 11 hours of rest between working days and either 24 hours off each week or 48 hours each fortnight (gov.uk: rest breaks at work). The working week is limited to 48 hours on average, normally averaged over 17 weeks, unless the worker opts out (gov.uk: maximum weekly working hours).

In the US, federal law sets no limit on daily or weekly hours for employees aged 16 and over. Covered employees who are not exempt must be paid at least time and a half for hours over 40 in a workweek (U.S. Department of Labor: overtime pay). State laws and union contracts can add rules.

The template checks the rest between two days: for every cell from the second day on, a rule compares the end of yesterday's shift with the start of today's. With less than the hours set in Shifts!H5 (11 by default), the code turns red and bold. The classic case is a late shift until 22:00 followed by an early shift at 6:00: only 8 hours. On the Shifts sheet, a shift over 6 hours with a break under 20 minutes turns red. US users can clear H5 to switch the rest check off. The template does not know your contract or local law and is not legal advice.

Common mistakes

Night shift with negative hours

6:00 minus 22:00 is negative without MOD, and Excel shows ####. MOD(end-start,1) gets every shift across midnight right. More in the lesson on calculating time in Excel.

Codes with a trailing space

"E " with a space is not "E" to COUNTIF, and the hours go missing. The drop-down stops that kind of typo.

Times typed with a period

6.30 is the number six point three, not a time. Always type times with a colon.

Work schedule formulas in German Excel

English	German
SUMPRODUCT, COUNTIF	SUMMENPRODUKT, ZÄHLENWENN
MOD, WEEKDAY, VLOOKUP	REST, WOCHENTAG, SVERWEIS
ISOWEEKNUM, WEEKNUM	ISOKALENDERWOCHE, KALENDERWOCHE

Which Excel versions support this?

SUMPRODUCT, COUNTIF, MOD, WEEKDAY, WEEKNUM, drop-down lists and conditional formatting work in all current versions. Microsoft lists ISOWEEKNUM for Excel 2016 and later, Microsoft 365 and Excel for Mac.

FAQ

More files are on the Excel templates page.