DEV Community

Ashish Srivastava
Ashish Srivastava

Posted on Originally published at ashishexcelnotes.hashnode.dev

A simple overdue-invoice aging formula for freelancers in Excel

#excel #freelancing #productivity #tutorial

If you freelance, you probably know roughly who owes you money but not exactly how late each payment is. An aging report fixes that. It groups unpaid amounts by how many days they're overdue: 0–30, 31–60, 61–90 and 90+. Here's how to build one in plain Excel with formulas that work in older versions too.

The invoice table

One row per invoice, starting in row 2:

Col Field
B Invoice date
C Client
J Total
L Due date
M Amount received
O Balance
Q Days overdue

Due date (L2): invoice date plus payment terms. With terms in K2:

=IF($B2="","",B2+K2)
Enter fullscreen mode Exit fullscreen mode

Balance (O2):

=IF($B2="","",J2-N(M2))
Enter fullscreen mode Exit fullscreen mode

N() turns an empty "received" cell into 0, so new invoices don't break.

Days overdue (Q2):

=IF($B2="","",IF(AND(O2>0,TODAY()>L2),TODAY()-L2,0))
Enter fullscreen mode Exit fullscreen mode

Paid invoices and invoices that aren't due yet both get 0. Only unpaid, past-due invoices count up.

Status (P2), which is handy for filtering:

=IF($B2="","",IF(O2<=0,"Paid",IF(TODAY()>L2,"Overdue",IF(N(M2)>0,"Part-paid","Open"))))
Enter fullscreen mode Exit fullscreen mode

Why every formula starts with IF($B2="","",…)

You'll fill the formulas down a few hundred rows in advance. Without the check, empty rows would show 0 or errors. With it, a row stays blank until you type a date.

The aging summary

On a second sheet, list each client in column A. For each bucket, add up the balance where the client matches and days overdue falls in the range:

0–30:  =SUMIFS(Inv!$O:$O,Inv!$C:$C,$A2,Inv!$Q:$Q,">=0",Inv!$Q:$Q,"<=30")
31–60: =SUMIFS(Inv!$O:$O,Inv!$C:$C,$A2,Inv!$Q:$Q,">=31",Inv!$Q:$Q,"<=60")
61–90: =SUMIFS(Inv!$O:$O,Inv!$C:$C,$A2,Inv!$Q:$Q,">=61",Inv!$Q:$Q,"<=90")
90+:   =SUMIFS(Inv!$O:$O,Inv!$C:$C,$A2,Inv!$Q:$Q,">=91")
Enter fullscreen mode Exit fullscreen mode

The 0–30 bucket also includes invoices that aren't due yet, because their days overdue is 0. If you'd rather keep "not yet due" separate, add a column with Inv!$Q:$Q,0 and Inv!$P:$P,"Open" as the criteria, and start the first overdue bucket at 1.

Quick check: the four buckets should add up to the client's total balance. Put =SUM(B2:E2) next to =SUMIFS(Inv!$O:$O,Inv!$C:$C,$A2). If the two numbers differ, a row has a negative balance (an overpayment) or a typo in a client name.

If you bill in more than one currency

Don't add USD and EUR together. Add a currency column to the invoice table and one more SUMIFS condition, Inv!$F:$F,"USD". That gives you one aging table per currency. Converting currencies is a separate decision, made at your own rate on your own date.

Common mistakes

  1. Client names typed differently, like "Acme Ltd" and "ACME Ltd.". Use a dropdown from a client list.
  2. Hard-coding today's date. TODAY() keeps the aging current every time you open the file.
  3. Overwriting the formula columns. Shade them grey so you know not to type in them.

What to do with it

Sort clients by the 61–90 and 90+ columns. Those are this week's follow-up emails. The polite first reminder goes out at around 7 days overdue, and a firmer one at 30.

I drafted this article with AI tools and edited it before publishing. It's general spreadsheet guidance, not accounting or legal advice.

Top comments (0)