# How to find who is on duty for a date in a rotation in Excel

Count whole weeks since the rotation started and wrap around the list: `=INDEX(A2:A5,MOD(INT((G1-E1)/7),ROWS(A2:A5))+1)`.

**Excel:** `=INDEX(A2:A5,MOD(INT((G1-E1)/7),ROWS(A2:A5))+1)` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F | G |
| --- | --- | --- | --- | --- | --- | --- |
| On-call order |  |  | Rotation start | 2026-01-05 | Date | 2026-03-18 |
| Marcus |  |  | On call |  |  |  |
| Priya |  |  |  |  |  |  |
| Tom |  |  |  |  |  |  |
| Rosa |  |  |  |  |  |  |

Formula in E2: `=INDEX(A2:A5,MOD(INT((G1-E1)/7),ROWS(A2:A5))+1)`

Result: Tom

## How it works

From January 5 to March 18 is 72 days, and `INT(72/7)` is 10 full weeks. MOD(10,4) is 2, because the four people have gone round twice and two more weeks have passed; adding 1 turns that into position 3, Tom. When the rotation grows, ROWS picks up the new length.

## Other ways

- `=CHOOSE(MOD(INT((G1-E1)/7),4)+1,A2,A3,A4,A5)`: CHOOSE lists the people directly; fine for a short, fixed rotation.

## Common mistakes

- For a daily rotation, drop the division: `MOD(G1-E1,ROWS(A2:A5))+1`.
- The start date must be the first day of Marcus's turn. A start in the middle of a week shifts every handover day.

## Questions

**Does it work for dates before the start?**

Yes. MOD returns a positive remainder for negative numbers in Excel and Google Sheets, so the rotation runs backwards correctly.

**How do I list the next four weeks?**

Use SEQUENCE: `=INDEX(A2:A5,MOD(INT((G1-E1)/7)+SEQUENCE(4,1,0),ROWS(A2:A5))+1)`.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/duty-rotation-for-date