How to Manage Recurring Tasks: A Full Year of Dates in One Sheet
How to manage recurring tasks, in one sentence: write each routine down once with its frequency and first date, generate every occurrence for the year as its own row, and mark each row done, skipped or moved as the year goes on. Keeping the rule (the task and how often) apart from the occurrences (the dates) is what lets you see what slipped, instead of one checkbox that resets every week.
Recurring Task Frequency Types
Most routines fit one of these patterns. The counts are for 2027, from the first date shown:
| Frequency | Example task | First date | Next date | Occurrences in 2027 |
|---|---|---|---|---|
| Every workday | Clear the shared inbox | Mon, Jan 4 | Next weekday | 260, before holidays |
| Weekly | Client status report | Fri, Jan 8 | 7 days later | 52 |
| Every 2 weeks | Payroll check | Fri, Jan 8 | 14 days later | 26 |
| Twice a month | Reconcile accounts | Fri, Jan 1 | The 1st and the 15th | 24 |
| Monthly by date | Send invoices | Sun, Jan 31 | Same day next month, or its last day | 12 |
| Monthly by weekday | Planning meeting | Mon, Jan 4 | First Monday of the month | 12 |
| Quarterly | Review subscriptions | Fri, Jan 15 | 3 months later | 4 |
| Yearly | Renew the domain name | Wed, Mar 10 | 12 months later | 1 |
| After completion | Replace the furnace filter | Last change | Last done date + 90 days | Depends |
Most routines run on a fixed schedule: the report is due every Friday, even if last week’s was late. A few should float instead, like the filter change that comes 90 days after the last actual change.
For tasks you do every day, 260 rows add more noise than help. Put them on a weekly checklist and keep the schedule for weekly and longer cycles.
How to Manage Recurring Tasks in a Spreadsheet
Use two tabs: one for the rules and one for the dates.
- On a Rules tab, list each recurring task once: task, frequency, first date, owner and notes.
- On a Schedule tab, set up column A for Task, B for Planned, C for Moved to, D for Due, E for Status and F for Done on.
- Generate each task’s dates with the formulas in the next section, in a spare area of the sheet.
- Copy the dates and paste them into column B as values only (Google Sheets: Edit > Paste special > Values only; Excel: Home > Paste > Paste Values). Fill the task name down column A beside them.
- Add a dropdown to column E with Open, Done and Skipped (Data > Data validation in Google Sheets; Data > Data Validation > List in Excel).
- Sort the schedule by column D so the year reads in date order, and filter it to the current week when you work.
Pasting values matters. A formula keeps recalculating, so if you later change a first date, every date shifts while the statuses you typed stay put, and your Done marks end up next to the wrong dates.
Formulas That Generate a Year of Dates
Put the task’s first date in B1 and the last date of the year in B2 (12/31/2027). These fill-down formulas work in every version of Google Sheets and Excel. If a result shows as a number such as 46395 (the serial number for January 8, 2027), format the column as dates: Format > Number > Date in Google Sheets, or Short Date in Excel’s number format box.
Weekly. In A5, enter =$B$1. In A6, enter this and copy it down to A57, enough rows for 53 weeks:
=IF(A5="", "", IF(A5+7>$B$2, "", A5+7))
Each row adds 7 days to the one above and goes blank after the end date. Change both 7s to 14 for every two weeks.
Monthly. In A5, enter this and copy it down 12 rows:
=IF(EDATE($B$1, ROWS($A$5:A5)-1)>$B$2, "", EDATE($B$1, ROWS($A$5:A5)-1))
ROWS($A$5:A5)-1 counts 0, 1, 2 and so on as you copy down, so every date is calculated from the first date. For quarterly, change both ROWS($A$5:A5)-1 to (ROWS($A$5:A5)-1)*3; for yearly, multiply by 12. Twice a month is simply two monthly rules, one starting on the 1st and one on the 15th.
This matters near month end. EDATE keeps the day of the month, or uses the last day of a shorter month, so a formula chained from the previous row gets stuck after February:
| Month | From the first date: EDATE($B$1, n) | Chained: EDATE(previous row, 1) |
|---|---|---|
| January | Jan 31 | Jan 31 |
| February | Feb 28 | Feb 28 |
| March | Mar 31 | Mar 28 |
| April | Apr 30 | Apr 28 |
| December | Dec 31 | Dec 28 |
First Monday of each month. In A5, enter this and copy it down 12 rows for 2027:
=DATE(2027, ROWS($A$5:A5), 1) + MOD(2 - WEEKDAY(DATE(2027, ROWS($A$5:A5), 1)), 7)
WEEKDAY numbers the days 1 for Sunday through 7 for Saturday, so the 2 means Monday. Use 6 for Friday. Add 7 at the end for the second week of the month and 14 for the third. For 2027 the formula returns Jan 4, Feb 1, Mar 1, Apr 5 and so on through Dec 6.
Every workday, skipping holidays. List your holidays in H2:H15. In A5, enter =WORKDAY($B$1-1, 1, $H$2:$H$15), which returns the first date itself if it is a workday. In A6, enter this and copy it down:
=IF(A5="", "", IF(WORKDAY(A5, 1, $H$2:$H$15)>$B$2, "", WORKDAY(A5, 1, $H$2:$H$15)))
WORKDAY skips Saturdays, Sundays and every date in the holiday list. For a different weekend, such as Friday and Saturday, use WORKDAY.INTL, covered with worked examples in our NETWORKDAYS in Google Sheets guide.
One-Formula Versions
With Google Sheets, Excel 2021 or Excel for Microsoft 365, one formula fills the whole column:
- Weekly, both apps:
=SEQUENCE(INT(($B$2-$B$1)/7)+1, 1, $B$1, 7) - Monthly in Excel:
=EDATE($B$1, SEQUENCE(12, 1, 0)) - Monthly in Google Sheets:
=MAP(SEQUENCE(12, 1, 0), LAMBDA(n, EDATE($B$1, n)))
With a first date of Friday, January 8, the weekly formula returns 52 dates, the last one December 31. SEQUENCE does not exist in Excel 2019 or earlier, so use the fill-down formulas there. Either way, paste the results as values before you type statuses next to them.
Handling Done, Skipped and Moved Occurrences
Each occurrence gets one of three outcomes:
- Done. Set the status to Done, and fill in Done on if the actual date matters.
- Skipped. Set the status to Skipped and never delete the row. A deleted row hides the gap; a skipped row shows it, which is what you need when a routine starts slipping.
- Moved. Type the new date in Moved to and leave Planned alone. The next occurrence keeps its original date, so one late report does not push back the rest of the year.
Three formulas, identical in Google Sheets and Excel, do the bookkeeping:
- Due date, in D2:
=IF(C2<>"", C2, B2) - Overdue check, in G2:
=AND(E2="Open", D2<TODAY()) - Next date for an after-completion task:
=MAXIFS(F2:F500, A2:A500, "Replace the furnace filter") + 90(MAXIFS needs Excel 2019 or later, or Microsoft 365)
Example: A Weekly Client Report
Say it is Wednesday, February 24, 2027, and the report has been due every Friday since January 8:
| Planned | Moved to | Due | Status |
|---|---|---|---|
| Fri, Jan 8 | Jan 8 | Done | |
| Fri, Jan 15 | Jan 15 | Done | |
| Fri, Jan 22 | Mon, Jan 25 | Jan 25 | Done |
| Fri, Jan 29 | Jan 29 | Done | |
| Fri, Feb 5 | Feb 5 | Skipped (client office closed) | |
| Fri, Feb 12 | Feb 12 | Done | |
| Fri, Feb 19 | Mon, Feb 22 | Feb 22 | Open, overdue |
| Fri, Feb 26 | Feb 26 | Open |
Seven occurrences are due by today. One was skipped on purpose and five of the other six are done: an 83% completion rate, with one overdue report to send. With the task name in H2, this formula gives the rate in both apps:
=COUNTIFS(A2:A500, H2, E2:E500, "Done", D2:D500, "<="&TODAY()) / (COUNTIFS(A2:A500, H2, D2:D500, "<="&TODAY()) - COUNTIFS(A2:A500, H2, E2:E500, "Skipped", D2:D500, "<="&TODAY()))
Format the cell as a percentage.
Keep Your Routine Task List Under Control
Once a month, spend 15 minutes on the schedule:
- Skipped three times this quarter? Change the frequency, or drop the task.
- Always moved to the same day? Move the schedule to that day.
- Always done by someone else? Make them the owner; how to delegate tasks has a handoff checklist.
- New routine? Add one row on the Rules tab and generate its dates.
Keep routines apart from project work. They never move through stages, so they belong on this schedule rather than on a personal kanban board. They still use up hours, though, so count them when you check capacity, as described in how to manage multiple projects.
Our project management spreadsheet uses the same split. Each recurring task takes a frequency and a first date, a schedule tab lists every occurrence for the year to tick off or adjust, and recurring tasks show up in the task filter and decision matrix next to one-off work, but not on the Kanban board, Gantt chart or project budget.
Start with the five routines that hurt most when they slip, not all thirty. Generate their dates for the year tonight, and add the rest one at a time as you notice them.
Frequently asked questions
What is the difference between a recurring task and a habit?
A recurring task has an output someone expects, such as a report or a paid bill, and missing it has a cost. A habit is a personal behavior you repeat for its own sake, like reading or exercise, and it suits a habit tracker better than a task schedule.
How do I catch up when recurring tasks pile up?
Do the most recent occurrence and mark the older ones as skipped, unless each one produces something still needed, such as separate invoices. Three stale weekly reports rarely help anyone.
How far ahead should I schedule recurring tasks?
A calendar year is a good default: long enough to show quarterly and yearly tasks, and easy to regenerate each January. For routines that change often, schedule one quarter at a time.