DEV Community

Pavlina Stavreva
Pavlina Stavreva

Posted on Edited on AI-assisted

How to track unpaid invoices in Google Sheets (with automatic overdue status)

Most freelancers don't lose money because clients refuse to pay. They lose it because nobody notices that an invoice is three weeks late.

If your invoices live in a folder of PDFs, you only see a late payment when you happen to look for it. A small sheet with one row per invoice fixes that. It takes about 15 minutes to build, and the formulas below do the checking for you.

1. One row per invoice

Create these columns in row 1:

A B C D E F G H
Invoice no. Client Amount Invoice date Due date Paid on Status Days overdue

You only ever type into A, B, C, D and F. Everything else is a formula.

2. Calculate the due date

Most invoices are due 14 or 30 days after the invoice date. In E2:

=IF(D2="", "", D2 + 30)
Enter fullscreen mode Exit fullscreen mode

Change 30 to your own payment terms. Format the column as a date (Format → Number → Date).

3. An automatic status

This is the useful part. In G2:

=IF(A2="", "", IF(F2<>"", "Paid", IF(TODAY() > E2, "Overdue", "Open")))
Enter fullscreen mode Exit fullscreen mode
  • If there is a payment date, the invoice is Paid.
  • If not, and today is past the due date, it is Overdue.
  • Otherwise it is still Open.

Because the formula uses TODAY(), an invoice moves from Open to Overdue on its own the day after it is due. You don't have to remember anything.

4. How many days late

In H2:

=IF(G2="Overdue", TODAY() - E2, "")
Enter fullscreen mode Exit fullscreen mode

Seeing "23" next to a client's name is a much stronger reminder than a red cell.

Copy E2:H2 down as far as you need (select the cells and drag the small square in the corner).

5. Make overdue invoices red

  1. Select columns A to H, from row 2 down.
  2. Format → Conditional formatting.
  3. Under "Format cells if", choose Custom formula is and enter:
=$G2="Overdue"
Enter fullscreen mode Exit fullscreen mode
  1. Pick a light red fill and click Done.

The $ before G is important: it makes the whole row turn red, not only the status cell.

6. The total that still has to come in

Somewhere above or beside the table, add:

Still to be paid:  =SUMIF(G:G, "<>Paid", C:C)
Overdue:           =SUMIF(G:G, "Overdue", C:C)
Enter fullscreen mode Exit fullscreen mode

SUMIF adds up the amounts in column C only for the rows whose status matches. The first line is everything not yet paid, the second only what is already late.

7. A five-minute weekly routine

Once a week:

  1. Enter the payment date for anything that arrived in your bank account.
  2. Sort or filter by status and look at the red rows.
  3. Send a friendly reminder for everything more than 7 days overdue.

That's all. A sheet like this won't chase clients for you, but it makes sure you never forget to.

If you want it ready-made

If you only need the invoice part, the Invoice Tracker has exactly this setup ready to use. For clients, hours and expenses too, there's Freelance Desk.

But the formulas above are everything you need to build your own for free.

How do you keep track of unpaid invoices today? I'm curious whether most people use a sheet, their accounting tool or just their inbox.

Top comments (1)

Collapse
 
ztrx_febb1a49041b23a5125a profile image
ztrx •

The overdue column is the useful part. One thing I would add: send the reminder from wherever the status lives, or the sheet stays accurate while the client still has not heard from you. The number I ended up sorting by was days since last outbound, not days since invoice - one that is 30 days old but chased yesterday is a very different problem from one that is 30 days old and never mentioned. Worth a second column if you use this daily.