Track Overdue Invoices Automatically in Google Sheets

When you manage several invoices in a spreadsheet, it is easy to miss a due date or spend time checking which clients still need to pay.

A simple Google Sheets invoice tracker can calculate how many days an unpaid invoice is overdue, mark paid invoices with a checkbox, and show a clear status automatically.

QUICK ANSWER

Use a due date, a Paid checkbox, and two simple formulas

Create columns for the invoice number, client, due date, payment checkbox, days overdue, and status. Google Sheets can then compare the due date with TODAY() and automatically show whether an unpaid invoice is Overdue, Due Today, or Open.

Why Track Overdue Invoices Automatically?

Manually checking due dates works when you only have a few invoices. As the list grows, it becomes much easier to overlook a late payment.

A formula-based tracker can update whenever the sheet recalculates, so you can quickly see which unpaid invoices are already past their due dates.

Set Up a Simple Invoice Tracker

For this example, use the following columns:

Column	Purpose
A - Invoice #	Invoice number or ID
B - Client	Client or company name
C - Due Date	Date payment is due
D - Paid?	Checkbox for completed payment
E - Days Overdue	Calculated number of late days
F - Status	Paid, Overdue, Due Today, or Open

Add a Paid Checkbox

Select the cells in column D where you want the payment checkboxes, then choose Insert > Checkbox.

Google Sheets uses TRUE for a checked checkbox and FALSE for an unchecked one, which means the checkbox can be used directly inside a formula.

Calculate the Days Overdue

In cell E2, enter:

=IF(OR(C2="",D2=TRUE),"",MAX(0,TODAY()-C2))

This formula does three useful things:

If the due date is blank: leave the result blank.
If the Paid checkbox is checked: leave the result blank.
If the invoice is unpaid: show the number of days past the due date, but never return a negative number.

EGGLOVER TIP

After entering the formula in E2, copy it down the rest of the Days Overdue column.

Show the Invoice Status Automatically

You can also make the Status column respond to both the due date and the payment checkbox.

In F2, use:

=IF(C2="","",IF(D2=TRUE,"Paid",IF(C2<TODAY(),"Overdue",IF(C2=TODAY(),"Due Today","Open"))))

The status will now behave like this:

Condition	Status
Paid checkbox selected	Paid
Due date already passed	Overdue
Due date is today	Due Today
Due date is still ahead	Open

Highlight Overdue Invoices Automatically

Once the Status column works, conditional formatting can make overdue invoices much easier to spot.

1. Select the invoice table, for example A2:F1000.
2. Go to Format > Conditional formatting.
3. Choose Custom formula is.
4. Enter =$F2="Overdue".
5. Choose the formatting style you want.
6. Click Done.

Why use the Status column?

Using the Status value for the conditional-formatting rule keeps the logic easy to understand. The formula decides the status, and the formatting simply responds to that status.

Example Invoice Tracker

Invoice	Client	Due Date	Paid?	Days Overdue	Status
INV-1001	Acme Co.	Past date	Unchecked	12	Overdue
INV-1002	Bright Ltd.	Past date	Checked		Paid
INV-1003	Cloud Inc.	Future date	Unchecked	0	Open

If the Formula Does Not Work

Check the date

Make sure the Due Date cell contains an actual date value rather than plain text.

Check the columns

If your Due Date or Paid columns are in different locations, change the cell references in the formulas.

Check the checkbox

A standard checkbox returns TRUE when checked and FALSE when unchecked.

Related Google Sheets Problems

Highlight Overdue Dates Automatically

Next article in the Invoice Tracker cluster.

Find Duplicate Invoice Numbers

Catch duplicate entries before they cause tracking problems.

Mark an Invoice as Overdue

Build a simpler status-only version of the tracker.

Frequently Asked Questions

Can Google Sheets calculate overdue days automatically?

Yes. A formula can compare a due date with TODAY() and calculate how many days have passed.

How do I stop paid invoices from showing as overdue?

Add a Paid checkbox and check its TRUE or FALSE value before calculating the overdue result.

Can an entire overdue row change color?

Yes. Apply conditional formatting to the full table range and use a custom formula that checks the Status column.

Does TODAY() update automatically?

TODAY() represents the current date when the spreadsheet recalculates, so overdue calculations can change as the date changes.

FINAL TAKEAWAY

Let the spreadsheet check the dates for you

A due date, Paid checkbox, Days Overdue formula, and automatic Status column are enough to create a simple invoice tracker that makes overdue payments much easier to spot.

SOURCES AND LAST CHECKED

This guide is based on current Google Sheets Editors Help documentation for TODAY, IF, checkboxes, formulas, and conditional formatting. Last checked: October 2026.