Excel guide · 5 min read
How to build a month-end close checklist in Excel
=WORKDAY(month_end, workday, holidays), add a status drop-down, and flag a task as overdue when =AND(status<>"Done", due_date<report_date). Then COUNTIFS gives % complete by owner and FILTER lists what's late. On the sample October close, 5 of 10 tasks are done and 3 are overdue.Sample data
Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.
| Row | A | B | C | D | E | F | G | H | I | J | K | L |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Task | Owner | Workday | Due date | Status | Overdue | Month end | Oct 31, 2026 | Owner | Done | ||
| 2 | AP cut-off and invoice accruals | Marco | 1 | Nov 2, 2026 | Done | FALSE | Report date | Nov 10, 2026 | Priya | 0.75 | ||
| 3 | Bank reconciliations | Priya | 2 | Nov 3, 2026 | Done | FALSE | Marco | 0.66666666667 | ||||
| 4 | Revenue and AR cut-off | Marco | 2 | Nov 3, 2026 | Done | FALSE | Holidays | Nov 11, 2026 | Sam | 0 | ||
| 5 | Accrued expenses journal | Priya | 3 | Nov 4, 2026 | Done | FALSE | Nov 26, 2026 | |||||
| 6 | Prepaids and deferred revenue | Sam | 3 | Nov 4, 2026 | In progress | TRUE | Dec 25, 2026 | |||||
| 7 | Fixed asset depreciation | Sam | 4 | Nov 5, 2026 | Not started | TRUE | ||||||
| 8 | Payroll reconciliation | Priya | 4 | Nov 5, 2026 | Done | FALSE | ||||||
| 9 | Intercompany reconciliation | Marco | 5 | Nov 6, 2026 | In progress | TRUE | ||||||
| 10 | Trial balance review | Priya | 7 | Nov 10, 2026 | Not started | FALSE | ||||||
| 11 | Management pack to CFO | Sam | 8 | Nov 12, 2026 | Not started | FALSE |
Download this sheet as an Excel practice file (.xlsx)
Set up the task list and the inputs
Use one row per task: the task in A, the owner in B, the close workday in C, the due date in D, the status in E and the overdue flag in F. The close workday is the number your team already uses: "bank recs on day 2" means the second working day after month end.
Keep everything that changes each month in input cells on the right: the month end in I1 (31 Oct 2026), the report date in I2 (10 Nov 2026, the day you check progress) and company holidays in I4:I6. Next month you change I1 and I2 and every due date and flag recalculates. You can put =TODAY() in I2 for a live view, but a typed date lets you see exactly what the sheet showed at a review meeting.
The task list itself is your team's process, not something Excel decides. The ten tasks here are a typical small-company close: cut-offs first, reconciliations and journals next, then the trial balance review and the pack for management.
Due dates in workdays with WORKDAY
In D2, =WORKDAY($I$1,C2,$I$4:$I$6) counts C2 working days forward from the month end, skipping Saturdays, Sundays and every date in the holidays range. Fill it down. The month end itself is day 0, so workday 1 is the first working day after it, even when the month ends on a weekend: 31 Oct 2026 is a Saturday, and day 1 is Monday 2 Nov.
The holiday matters on day 8. Without a holidays range, =WORKDAY(I1,8) lands on Wednesday 11 Nov; with 11 Nov listed as a company holiday, the management pack moves to Thursday 12 Nov. The holidays must be real dates, not text like "11 Nov", or WORKDAY can't use them. WORKDAY vs NETWORKDAYS explains the two functions side by side.
Result: Thu 12 Nov
Thu 12 Nov: workday 8 of the October close, one day later than without the 11 Nov holiday.
What about tasks before month end, like a pre-close?
Give them a negative workday. WORKDAY counts backwards from the month end, so -2 is two working days before it.
Result: Thu 29 Oct
Thu 29 Oct: two working days before Saturday 31 Oct (Friday 30 Oct is day -1).
How do I roll the checklist to next month?
Change I1 to the next month end, or let EOMONTH do it: =EOMONTH(I1,1) returns 30 Nov 2026. For the November close, workday 8 falls on Thu 10 Dec. If your company closes on a 4-4-5 calendar instead of calendar months, type each period end into I1 by hand.
A status drop-down and the overdue flag
A drop-down keeps the statuses identical, which matters because the formulas count the exact word Done. A typed "done " with a trailing space would never count. Create a drop-down list in Excel covers lists that come from a range instead.
In F2, =AND(E2<>"Done",D2<$I$2) returns TRUE when the task isn't done and its due date has passed. Fill it down. A task due on the report date itself isn't overdue yet: the trial balance review is due 10 Nov and shows FALSE. To colour late rows, select A2:F11, choose Home > Conditional Formatting > New Rule > Use a formula, enter =$F2 and pick a fill, as in highlight a row based on a cell value.
- Select E2:E11.
- Go to Data > Data Validation.
- Under Allow, choose List.
- In Source, type Not started,In progress,Done and click OK.
Result: 3
3: prepaids, fixed asset depreciation and the intercompany reconciliation are past their due dates and not done.
How many working days late is a task?
NETWORKDAYS counts working days including both ends, so subtract 1. The prepaids task in row 6 was due Wed 4 Nov.
Result: 4
4: four working days late on 10 Nov. Plain subtraction (I2-D6) would say 6, because it counts the weekend.
% complete overall and by owner
Overall progress is the number of Done tasks over the number of tasks. ROWS counts the rows in the range, so it keeps working when you insert tasks inside it.
By owner, list the names in K2:K4 and in L2 use =COUNTIFS($B$2:$B$11,K2,$E$2:$E$11,"Done")/COUNTIF($B$2:$B$11,K2): Done tasks for that owner over all their tasks. Fill it down. Priya is at 75%, Marco at 66.7% and Sam at 0%, which is where the follow-up goes. Counting tasks treats a five-minute task the same as a two-day one; if that misleads, add an estimated-hours column and weight by it, as in percentage of tasks completed.
Result: 0.5 shows as 50% with Percent format
0.5, 50% complete: 5 of the 10 close tasks are marked Done.
A live list of what's late with FILTER
For the daily close stand-up you want the late tasks only, not the whole list. In Excel 365 and 2021, FILTER returns the rows where column F is TRUE, and the third argument shows a message instead of #CALC! when nothing is late. The list updates as people change their status to Done.
To sort the late list by due date, wrap it: =SORT(FILTER(A2:D11,F2:F11),4) sorts the result on its fourth column, the due date. In older Excel, use the filter button on column F instead.
Result: spills 3 rows × 2 columns
| Prepaids and deferred revenue | Sam |
| Fixed asset depreciation | Sam |
| Intercompany reconciliation | Marco |
Spills 3 rows: Prepaids and deferred revenue (Sam), Fixed asset depreciation (Sam), Intercompany reconciliation (Marco).
Common mistakes
Typing due dates by hand each month
Store the workday number and calculate the date with WORKDAY from a month-end input cell, so rolling forward is one change.
Forgetting company holidays
List them as real dates in a range and pass it as WORKDAY's third argument. Use the same range in NETWORKDAYS.
Free-typed statuses
Use a Data Validation list so "Done" is always spelled the same way and COUNTIFS counts it.
Flagging tasks due today as overdue
Compare with < the report date, not <=, so a task is late only once its due date has passed.
FAQ
How do I calculate business day 3 after month end in Excel?
Use =WORKDAY(month_end,3,holidays). The month end counts as day 0, weekends are skipped, and any dates in the holidays range are skipped too.
How do I flag overdue tasks in Excel?
Use =AND(status<>"Done",due_date<report_date), with TODAY() or a typed date as the report date. Then highlight the rows with conditional formatting using a formula like =$F2.
Can Excel show % complete by person?
Yes. Divide COUNTIFS of the person's Done tasks by COUNTIF of all their tasks, for example =COUNTIFS(B:B,"Priya",E:E,"Done")/COUNTIF(B:B,"Priya").
Should I use one sheet per month for the close checklist?
Copying the sheet each month works well: change the month end and report date, reset the statuses to Not started, and you keep last month's sheet as a record of what finished when.
Functions used
WORKDAYDate after n working daysNETWORKDAYSWeekdays between datesANDTrue if all tests are trueCOUNTIFCounts cells that matchCOUNTIFSCounts with several conditionsROWSNumber of rows in a rangeFILTERKeeps matching rows, spillsSORTSorts a range, spillsEOMONTHLast day of a monthTEXTNumber to formatted text
Related guides
- How to build an accrued expenses schedule in Excel
- How to do a bank reconciliation in Excel
- How to make a trial balance in Excel
- WORKDAY vs NETWORKDAYS: what is the difference?
- How to list all dates between two dates in Excel
- How to create a Gantt chart in Excel
- How to track expiration dates in Excel
Updated