Cellwise Start free

Excel guide · 5 min read

How to build a month-end close checklist in Excel

Quick answerList each close task with an owner and the workday it's due on (day 1, 2, 3 after month end). Work out the date with =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.

RowABCDEFGHIJKL
1TaskOwnerWorkdayDue dateStatusOverdueMonth endOct 31, 2026OwnerDone
2AP cut-off and invoice accrualsMarco1Nov 2, 2026DoneFALSEReport dateNov 10, 2026Priya0.75
3Bank reconciliationsPriya2Nov 3, 2026DoneFALSEMarco0.66666666667
4Revenue and AR cut-offMarco2Nov 3, 2026DoneFALSEHolidaysNov 11, 2026Sam0
5Accrued expenses journalPriya3Nov 4, 2026DoneFALSENov 26, 2026
6Prepaids and deferred revenueSam3Nov 4, 2026In progressTRUEDec 25, 2026
7Fixed asset depreciationSam4Nov 5, 2026Not startedTRUE
8Payroll reconciliationPriya4Nov 5, 2026DoneFALSE
9Intercompany reconciliationMarco5Nov 6, 2026In progressTRUE
10Trial balance reviewPriya7Nov 10, 2026Not startedFALSE
11Management pack to CFOSam8Nov 12, 2026Not startedFALSE

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.

=TEXT(D11,"ddd d mmm")

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.

=TEXT(WORKDAY(I1,-2),"ddd d mmm")

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.

  1. Select E2:E11.
  2. Go to Data > Data Validation.
  3. Under Allow, choose List.
  4. In Source, type Not started,In progress,Done and click OK.
=COUNTIF(F2:F11,TRUE)

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.

=IF(F6,NETWORKDAYS(D6,I2,I4:I6)-1,"")

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.

=COUNTIF(E2:E11,"Done")/ROWS(E2:E11)

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.

=FILTER(A2:B11,F2:F11,"Nothing late")

Result: spills 3 rows × 2 columns

Prepaids and deferred revenueSam
Fixed asset depreciationSam
Intercompany reconciliationMarco

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

Related guides

Updated