How to Build a Rotating Shift Schedule in Excel

A rotating shift schedule in Excel repeats one pattern of shifts, such as two days, two nights and four days off, with each team starting at a different point in the cycle. Instead of typing every shift by hand, you type the pattern once and one formula, =INDEX(pattern,MOD(date-start+offset,length)+1), fills the whole calendar. This guide is for anyone who plans 24/7 or multi-team cover and wants a rota that extends itself for months.

If you only need a simple weekly grid with hand-typed shifts and cover checks, start with our guide to making a weekly shift schedule. This article focuses on the rotation itself.

What is a rotating shift schedule?#

A rotating schedule is a fixed sequence of shifts that repeats after a set number of days, called the cycle length. Each team works the same sequence, but starts at a different step, so the shifts are covered every day.

Here are three common examples of rotation patterns. They are illustrations only. The right pattern, shift length and rest time depend on your contracts and the working-time rules where you are, so check those before you publish a rota.

D is a day shift, N a night shift, W a working day and O a day off.

Step 1: Set up the start date and the pattern#

Every formula in the rota reads from two inputs: a start date and the pattern list. Put them where you can see them:

• B1: the start date, Monday 2 November 2026. This is "day 0" of the cycle.
• J2:J9: the pattern, one step per cell: D, D, N, N, O, O, O, O.
• B2: the cycle length, =COUNTA(J2:J9), which returns 8.

Then build the date row. In C4 type =$B$1, and in D4 type =C4+1. Fill D4 to the right for as many days as you need. Excel stores dates as whole numbers, one per day, so +1 is always the next day. The date basics lesson explains how this works.

Step 2: The INDEX and MOD formula#

The core of the rota is one formula that turns a date into a step number and then into a shift. For a single team, it is:

=INDEX($J$2:$J$9,MOD(C$4-$B$1,$B$2)+1)

Read it from the inside out:

1. C$4-$B$1 is the number of days since the start date. For 20 November it is 18. Subtracting one date from another is the simplest way to count the days between two dates.
2. MOD(18,8) is the remainder after dividing by 8, which is 2. MOD wraps any number of days back into the range 0 to 7, so the pattern repeats forever.
3. +1 turns the 0-to-7 position into a 1-to-8 row number, because INDEX starts counting at 1.
4. INDEX($J$2:$J$9,3) returns the third item in the pattern: N.

The $ signs keep the pattern, start date and cycle length fixed while you fill the formula across. The $ in C$4 keeps every row pointing at the date row. The INDEX function guide and our article on MOD and INT in Excel cover both functions in more detail.

Step 3: Stagger the teams with an offset#

Give each team an offset, the number of steps it is ahead in the cycle, and add it to the day count. Put team names in A5:A8 and offsets in B5:B8, then type this in C5 and fill it across and down:

=INDEX($J$2:$J$9,MOD(C$4-$B$1+$B5,$B$2)+1)

With offsets of 0, 2, 4 and 6, the first eight days look like this:

Every column has exactly one D and one N, so days and nights are covered by four teams. On 20 November, Team D's shift is MOD(18+6,8)+1, which is step 1: D.

To change the rotation, edit the pattern list and the offsets. Every cell updates. Offsets are easier to manage than a different start date for each team, because the date row stays the same for everyone.

Weekly rotations with INT and CHOOSE#

When a shift changes once a week rather than every day, count whole weeks instead of days. INT((C$4-$B$1)/7) divides the days by 7 and drops the remainder, so days 0 to 6 are week 0, days 7 to 13 are week 1, and so on. Wrap that with MOD and pick the shift with CHOOSE:

=CHOOSE(MOD(INT((C$4-$B$1)/7)+$B5,3)+1,"Early","Late","Night")

With offsets of 0, 1 and 2, three teams work Early, Late and Night in turn. In the week starting 2 November, Team A is Early, Team B Late and Team C Night. A week later they all move one step on. CHOOSE is handy when the list is short; see how CHOOSE picks a value by position. For longer lists, keep using INDEX on a pattern range.

Check cover and highlight weekends#

A rotation is only useful if it covers every shift, so add a count under each date column:

=COUNTIF(C5:C8,"N")

This counts the night shifts on that date; it should be 1 in every column. A row that shows 0 or 2 means an offset or the pattern is wrong. The weekly shift schedule guide shows fuller cover checks with COUNTIFS, such as a supervisor on every shift.

Weekend columns often need extra attention for pay or cover. Use a conditional formatting rule with =WEEKDAY(C$4,2)>5 to shade them; our article on highlighting weekends walks through it.

Common problems and fixes#

Most rota errors come from the inputs, not the formula:

• Every team shows the same shift. The offsets are missing, or the formula doesn't add $B5.
• The pattern seems one day off. Check that B1 is the date of step 1 for a team with offset 0. Changing B1 by one day shifts the whole rota.
• #VALUE! appears. A date was typed as text. Retype it as a real date, or use =DATE(2026,11,2).
• The cycle length is wrong. If you add steps to the pattern, update the range, or the cycle length cell, so MOD wraps at the right point.
• Dates before the start date. These still work: Excel's MOD returns 5 for MOD(-3,8), which is the correct step when you count backwards.

Practise it#

Try the exercise on this page to find Team D's shift with INDEX and MOD. Then practise date formulas in the date basics lesson and practise counting the days between dates in a live spreadsheet that checks each answer as you type.