Make a Weekly Shift Schedule in Excel

A weekly shift schedule in Excel is a grid of people and days filled with short shift codes, plus a few formulas that count hours and check cover. You type the week's start date once, the other dates fill themselves, and COUNTIF and COUNTIFS flag any day that is short of staff or missing a supervisor. This project suits anyone who plans a team's week, and it is good practice for HR and operations roles.

What the finished schedule looks like#

The schedule is a grid: one row per person, one column per day, and one short code in each cell. Codes keep the grid readable and make counting easy.

This example uses three codes: E (early, 8 hours), L (late, 8 hours) and OFF. The week starts on Monday 2 November 2026.

Rows 3 and 4 are formulas, not typing. The next step shows how.

Step 1: Make the dates fill themselves#

Type the first date once and build every other date from it. In A1 type Week starting, and in B1 type the Monday's date. Then:

Excel stores dates as numbers, one per day, so +1 is the next day. Our guide to why Excel dates are numbers explains this. Use "dddd" in TEXT for full names such as Monday.

Start on the right Monday every time#

If someone types a Wednesday by mistake, this returns the Monday of that week:

=B1-WEEKDAY(B1,2)+1

WEEKDAY(date,2) numbers Monday as 1 and Sunday as 7. For Wednesday 4 November it returns 3, so the formula subtracts 3 and adds 1, landing on 2 November. To roll to next week, change B1 or use =B1+7. For a monthly rota, =EDATE(B1,1) moves a date one month forward and =EOMONTH(B1,0) gives the month's last day; see EDATE and EOMONTH.

Step 2: Fill in shifts with a drop-down list#

A drop-down list keeps every code spelled the same way, which COUNTIF depends on. "E", "e " and "Early" would each count differently.

1. Select the grid, C5:I9.
2. Go to Data > Data Validation (Windows: Alt, A, V, V; on a Mac, Data > Data Validation).
3. Under Allow, choose List, and in Source type E,L,OFF.
4. Click OK. Each cell now has a small arrow.

See create a drop-down list in Excel for more options. To colour the codes, select the grid and use Home > Conditional Formatting > Highlight Cells Rules > Equal To, once for each code.

Step 3: Count shifts and hours per person#

COUNTIF counts the cells in a row that match a code. Add two columns at the end of each row:

"<>OFF" means "not equal to OFF". Filled down, the results are Ana 5, Ben 6, Chen 5, Dina 5 and Eli 6 shifts, or 40, 48, 40, 40 and 48 hours. Ben and Eli are over a 5-shift week, which is exactly what this column is for.

One catch: "<>OFF" also counts blank cells. If some cells may be empty, add a second condition with COUNTIFS so blanks are skipped:

=COUNTIFS(C5:I5,"<>OFF",C5:I5,"<>")

If shifts have different lengths, count each code and multiply: =COUNTIF(C5:I5,"E")*8+COUNTIF(C5:I5,"L")*8. For start and finish times, see calculate hours worked and overtime.

Step 4: Check cover for each day with COUNTIFS#

Cover checks count down each day's column. Put them under the grid and fill right from C to I:

Row 13 uses COUNTIFS because it has two conditions: the role is Supervisor and the person isn't off. The role range is locked with $ so it stays on column B as you fill right, while the day column moves.

In this example, every day shows OK except Sunday, which shows Check: only one late shift (Chen) and no supervisor, because Ana and Dina are both off. Swap one shift and the flag updates. The COUNTIFS function guide and COUNTIFS and SUMIFS explained cover more criteria, such as counting a range of values.

Step 5: Spot weekends and holidays#

Weekend shifts often need a different pay rate or extra cover. This formula returns TRUE for Saturday and Sunday:

=WEEKDAY(C3,2)>5

Use it as a conditional formatting rule to shade weekend columns: select C3:I9, choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format, and enter =WEEKDAY(C$3,2)>5. The $ before 3 keeps every row pointing at the date row. Our WEEKDAY function guide has more examples.

How to extend the schedule#

Once the week works, these additions make it a full rota:

1. Four weeks on one sheet: copy the block below itself and set its start date to the cell above plus 7.
2. A night shift: add N to the drop-down source, a row 15 count, and N's hours in column K.
3. Leave and training codes: add codes such as AL (annual leave). Decide whether "<>OFF" should count them, or count only E, L and N.
4. Weekly totals: a SUMIFS on a separate summary sheet can add hours by person across weeks. The KPI summary sheet project shows that pattern.
5. Print it: set the page to landscape and fit to one page wide.

Scheduling and headcount reports are everyday work in people teams; Excel skills for HR and recruiting jobs lists others. The same "inputs, formulas, checks" layout also works for a monthly budget and a price list with discounts and tax.

Practise it#

Try the supervisor check in the exercise on this page. Then take the COUNTIFS and SUMIFS lesson to practise multi-condition counts, and the EDATE, EOMONTH and WEEKDAY lesson to get comfortable with the date formulas that drive the schedule.