Invoice tracker spreadsheet: formulas for paid, due and overdue
The most useful thing an invoice log can do is tell you, without you checking, which invoices need a nudge. Three formulas get you there.
The columns
Put headers in row 5 and data from row 6. Columns A to F are typed by you; G and H are formulas.
- A Invoice #, B Client
- C Issue date, D Due date, E Amount
- F Date paid (leave empty until the money arrives)
- G Status, H Days outstanding
Optional shortcut: if you always give 14 days, make the due date a formula, =C6+14, and overwrite it when a client has different terms.
Formula 1: status
=IF(E6="", "",
IF(F6<>"", "Paid",
IF(TODAY()>D6, "Overdue", "Due")))Read it as: no amount, show nothing; a payment date exists, it's Paid; otherwise it's Overdue if today is past the due date and Due if not. TODAY() updates whenever the file is opened, so statuses refresh themselves.
Formula 2: days outstanding
=IF(OR(E6="", F6<>""), "", MAX(0, TODAY()-C6))Counts days since the invoice was issued, only for invoices that are still unpaid. Paid and empty rows stay blank.
Formula 3: what you're still owed
Outstanding =SUMIFS(E6:E1005, G6:G1005, "Due")
+SUMIFS(E6:E1005, G6:G1005, "Overdue")
Overdue =SUMIFS(E6:E1005, G6:G1005, "Overdue")Put these in a small summary block beside the table. Total outstanding feeds your cash-flow forecast; the overdue figure is your to-do list.
Make overdue impossible to miss
Add conditional formatting to column G: text equal to Overdue in bold red, Paid in green. In Excel it's Home > Conditional Formatting > Highlight Cells Rules > Equal To. In Google Sheets it's Format > Conditional formatting > Text is exactly.
A follow-up routine that doesn't feel awkward
- Send a friendly reminder a few days before the due date for large invoices.
- The day after it goes overdue, send a short note with the invoice attached and the payment details again. Most late payments are oversight, not refusal.
- Pick your own cut-off for pausing new work for a client with an unpaid balance, and put it in your terms before you need it.
Already built
The Freelancer Finance Spreadsheet Kit has this invoice log with the status, days outstanding, totals and red/green highlighting already set up, plus an income and expense tracker, P&L, cash-flow forecast and tax set-aside calculator. One Excel file, $29.
General information about using spreadsheets, not financial, legal or accounting advice. Payment terms and what you can do about late payment depend on your contract and where you live.