📎 This article includes 1 downloadable practice file ↓

Every month-end, finance asks the same question: how much money is stuck, and for how long? An ageing report puts each unpaid invoice into a bucket — not due, 0–30 days, 31–60, 61–90, over 90 — and totals each bucket by customer. Excel does it with a days calculation and a lookup.

1. Days overdue

=IF(L2 = "Paid", "", MAX(0, $Q$1 - M2))

M2 is the due date, Q1 holds the report date (use a fixed date like 31-Mar, not TODAY(), so the report doesn’t change tomorrow). MAX(0, …) turns “not yet due” into 0 days.

2. Bucket label

With IFS

=IF(N2 = "", "", IFS(N2 = 0, "Not due", N2 <= 30, "0-30", N2 <= 60, "31-60", N2 <= 90, "61-90", TRUE, "90+"))

With a lookup table (easier to change)

=XLOOKUP(N2, Buckets[From], Buckets[Bucket], , -1)          ' 365: exact or next smaller
=VLOOKUP(N2, Buckets, 2, TRUE)                              ' any version (table sorted ascending)

Change the slabs in the table, not in every formula.

3. Totals per bucket and customer

Customers down column S, buckets across T1:X1:

=SUMIFS($I$2:$I$201, $D$2:$D$201, $S2, $O$2:$O$201, T$1)

Add a total column and a percentage row (bucket ÷ total outstanding) to show how much is seriously overdue.

4. Or a pivot table

With the Bucket column in place, insert a pivot: Customer in Rows, Bucket in Columns, Amount in Values. Order the bucket columns by dragging, or prefix labels with numbers (“1. 0-30”) so they sort correctly.

Where people go wrong

• Ageing from invoice date when policy says due date — agree which one the business uses.
• Using TODAY() — the report changes every day; month-end reports need a fixed date.
• Paid invoices included — filter them out or bucket them as blank.
• VLOOKUP table not sorted — approximate match gives nonsense on unsorted slabs.
• Credit notes and part-payments — age the balance, not the original invoice amount.

Practice

Download the combo practice workbook below: a Sales sheet of 200 orders plus tasks for the formulas on this page, each with an automatic ✓ check and an Answers sheet.

More: all formula combos · Excel function course.