How do I find overdue invoices in Excel?

Calculate each invoice's balance (total − SUMIFS of payments) and days overdue (TODAY() − due date), then filter balance > 0 and days > 30.

How do I build an aged debt report?

Put each open invoice into a bucket (0–30, 31–60, 60+ days) and sum balances per bucket and customer.

What about part-paid invoices?

Summing payments per invoice handles them: the balance is what's still outstanding.

Should I use TODAY() in the report?

Use a fixed report date in a cell instead, so a saved report keeps its numbers when reopened.

How do I total the debt per customer?

Group the open invoices by customer and sum the balance, optionally per ageing bucket.

What if a payment covers several invoices?

Split it across the invoices it pays before joining, or allocate it to the oldest invoices first if your policy allows.

Which invoices are more than 30 days overdue?

By the Analistable team · Updated 2026-10-07 · 3 min read

Sum payments per invoice, subtract from the invoice total to get the balance, and calculate days overdue = today − due date. Keep invoices with a balance above zero; filter those more than 30 days overdue for chasing. On 7 October, only INV-302 (Hart & Co, 600 outstanding, 38 days overdue) qualifies.

Part of our guide: How to answer questions across multiple spreadsheets

The data

Invoices and payments
Invoice	Customer	Due	Total	Paid
INV-301	Bakery Lune	2026-08-15	480	480
INV-302	Hart & Co	2026-08-30	1200	600
INV-303	Nordic Supply	2026-09-20	350	0
INV-305	Kiln Studio	2026-10-01	220	0

In a spreadsheet

Paid         =SUMIFS(Payments[Amount], Payments[Invoice], [@Invoice])
Balance      =[@Total] - [@Paid]
Days overdue =MAX(0, TODAY() - [@Due])
Bucket       =IF([@Balance] <= 0, "Paid", IF([@[Days overdue]] > 60, "60+", IF([@[Days overdue]] > 30, "31–60", "0–30")))

In SQL

SELECT i.invoice, i.customer, i.due, i.total,
       COALESCE(SUM(p.amount), 0) AS paid,
       i.total - COALESCE(SUM(p.amount), 0) AS balance,
       CAST(julianday('2026-10-07') - julianday(i.due) AS INTEGER) AS days_overdue
FROM invoices i
LEFT JOIN payments p ON p.invoice = i.invoice
GROUP BY i.invoice
HAVING balance > 0
ORDER BY days_overdue DESC;

Result
invoice	customer	due	total	paid	balance	days_overdue
INV-302	Hart & Co	2026-08-30	1200	600	600	38
INV-303	Nordic Supply	2026-09-20	350	0	350	17
INV-305	Kiln Studio	2026-10-01	220	0	220	6

Add AND days_overdue > 30 to keep only INV-302. The full list is an aged-debt report; group balances by bucket for the summary.

Watch out for

Payments recorded against the customer rather than the invoice — allocate them first, or the invoice looks unpaid.
Credit notes: subtract them from the balance like payments.
Use the due date, not the invoice date, unless your terms say otherwise.

Ask it in Analistable: “Which invoices are more than 30 days overdue, and how much does each customer owe in total?” Matching the payments in the first place: matching Stripe payments to invoices.

In Google Sheets

=FILTER(A2:E, E2:E > 0, TODAY() - C2:C > 30)

With invoice, customer, due date, total and balance in columns A–E, this lists open invoices more than 30 days past due.

The aged-debt summary for the example

Outstanding balances on 7 October
Bucket	Invoices	Balance
0–30 days	INV-303, INV-305	570
31–60 days	INV-302	600
60+ days	—	0
Total	3	1170

SELECT CASE WHEN days_overdue > 60 THEN '60+'
            WHEN days_overdue > 30 THEN '31-60'
            ELSE '0-30' END AS bucket,
       COUNT(*) AS invoices, SUM(balance) AS balance
FROM open_invoices
GROUP BY bucket;

Here open_invoices is the result of the query above. INV-301 is fully paid and drops out. The bucket totals must add up to the total outstanding: 350 + 220 + 600 = 1,170.

Use a report date, not TODAY()

TODAY() recalculates every time the file opens, so last month's aged-debt report changes when you reopen it. Put the report date in a cell (for example Report!B1 = 07/10/2026) and calculate =MAX(0, Report!$B$1 - [@Due]). The numbers then stay reproducible and you can rerun any past month by changing one cell.

Troubleshooting

Common problems
Symptom	Cause	Fix
Paid invoice still listed	Payment reference uses a different format (302 vs INV-302)	Normalise references before SUMIFS
Negative balance	Overpayment or payment applied to the wrong invoice	Review and reallocate
Days overdue in the thousands	Due date blank, treated as 0	Flag missing due dates separately
Customer total differs from the ledger	Credit notes not included	Subtract credit notes like payments

Monthly routine

Export invoices and payments to the same report date, refresh the join and save the bucket summary.
Sort the 31–60 and 60+ lists by balance and chase the largest first.
Compare the total outstanding with your ledger's receivables balance; a difference means unallocated payments or missing invoices — see reconciling a bank statement with the general ledger.

Frequently asked questions

How do I find overdue invoices in Excel?: Calculate each invoice's balance (total − SUMIFS of payments) and days overdue (TODAY() − due date), then filter balance > 0 and days > 30.
How do I build an aged debt report?: Put each open invoice into a bucket (0–30, 31–60, 60+ days) and sum balances per bucket and customer.
What about part-paid invoices?: Summing payments per invoice handles them: the balance is what's still outstanding.
Should I use TODAY() in the report?: Use a fixed report date in a cell instead, so a saved report keeps its numbers when reopened.
How do I total the debt per customer?: Group the open invoices by customer and sum the balance, optionally per ageing bucket.
What if a payment covers several invoices?: Split it across the invoices it pays before joining, or allocate it to the oldest invoices first if your policy allows.