How do I assign a rotating duty fairly across a team using a spreadsheet?
Quick answer
Keep the agreed rotation order on a roster sheet and record each scheduled duty in a separate log. Include the date, sequence number, scheduled person, actual person, status, and a short note. Agree in advance whether absences defer or skip a turn, and whether covering a duty counts as the coverer’s own turn. Record swaps and changes without overwriting the original assignment, then review pending turns and the next person together.
The question, the details, the next step.
I need to rotate a recurring duty across a team. How should I lay out the spreadsheet so it’s clear who is next and we can see whether turns have been fair?
Use two sheets: one for the agreed rotation order and another for scheduled duties and what happened. On the roster sheet, list each person’s position in the order, name, and whether they are active. On the assignment log, use one row per scheduled duty with columns for date, sequence number, scheduled person, actual person, status, and notes. Keep the original order visible rather than changing it to reflect a one-time absence or swap.
Before starting, agree what “fair” means for your team: equal turns, comparable workload, or another measure. If duties take different amounts of effort, a simple count of turns may not tell the whole story. For roster changes, note when they take effect and agree where a new person enters the sequence.
What should happen when the scheduled person is away? I don’t want an absence to quietly erase their turn, but the duty still needs to be covered.
Choose an absence rule in advance and apply it consistently. With a defer rule, the absent person’s turn stays pending; someone else covers that date, and the missed turn is scheduled later. With a skip rule, the rotation moves on and the absent person’s turn passes. Neither rule is automatically fairer—the important thing is that the team understands which applies.
Also decide whether covering someone else counts as the coverer’s own turn or is extra. For example, suppose the order is A. Lee, B. Noor, then C. Diaz, and the team chooses defer. If A is away on May 6 and B covers as an extra duty, record the row as:
Date | Scheduled | Actual | Status | Notes
May 6 | A. Lee | B. Noor | Absent—deferred | A’s turn remains pending; B covered extra
The next row can remain B’s scheduled turn if that is the agreed rule. Later, add a row for A’s deferred turn and mark it completed when taken. This keeps the coverage and the original turn distinguishable.
How do we log a voluntary swap without making it look like the original schedule was different?
Leave the scheduled person as originally listed and record who actually did the duty. For example, if B. Noor and C. Diaz exchange their May 13 and May 20 turns, keep each row’s original scheduled person, enter the other person as the actual person, mark the status “Swap,” and note the agreed exchange on both rows.
If a swap changes which future turn someone takes, name the dates or sequence numbers in the note. Don’t edit the roster to show a one-time exchange. If the swap does not happen as planned, update the status and record the team’s decision about the remaining turn instead of deleting the original entry.
Can you give me a simple routine for updating and checking the sheet so the next person is obvious?
After each duty, fill in the actual person, status, and any relevant note. Before assigning the next date, follow the agreed rotation rule and check whether deferred turns are pending. You can add a “Next up” column or label the next scheduled row. Use text as well as color so the person is still identifiable without relying on highlighting.
At a regular review, such as after a roster change or at a team-agreed interval, check that every row has a status, swaps are documented, and deferred turns are either completed or clearly pending. Compare assignments using the fairness measure the team chose, and account for roster changes or differences in duty effort where relevant. Keep notes brief: a general “unavailable” entry is usually enough if personal details are not needed.
A practical checklist
Agree whether absences defer or skip turns.
Decide whether covering another person’s turn counts as the coverer’s own turn.
Keep the roster order separate from the assignment log.
Record the scheduled person, actual person, status, and concise notes for each duty.
Document swaps without overwriting original assignments.
Review the next scheduled person and any pending turns.
A few more useful answers
Should I use a formula to choose the next person?
You can consider a formula after the team has settled its rules for absences, swaps, and deferred turns. A formula can follow a defined process, but it cannot choose those policies for you.
What if someone joins or leaves the team?
Record the change and its effective date, then agree where the new person enters the sequence. Keep completed assignment rows unchanged so the history remains clear.
How often should we check whether the rotation is fair?
Choose a review point that works for the team, such as after a roster change or on a regular schedule. Check pending turns and compare assignments against the fairness measure you agreed to use.