# How to calculate days overdue in Excel

Count from the due date to the paid date, or to today if unpaid, and floor it at zero: `=MAX(0,IF(C2="",$G$1,C2)-B2)`.

**Excel:** `=MAX(0,IF(C2="",$G$1,C2)-B2)` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F | G |
| --- | --- | --- | --- | --- | --- | --- |
| Invoice | Due date | Paid on | Days overdue |  | As of | 2026-10-29 |
| INV-2201 | 2026-09-15 | 2026-09-12 |  |  |  |  |
| INV-2202 | 2026-09-30 | 2026-10-14 |  |  |  |  |
| INV-2203 | 2026-10-10 |  |  |  |  |  |
| INV-2204 | 2026-10-31 |  |  |  |  |  |
| INV-2205 | 2026-08-31 | 2026-10-02 |  |  |  |  |
| INV-2206 | 2026-09-01 |  |  |  |  |  |

Formula in D2, filled to D7: `=MAX(0,IF(C2="",$G$1,C2)-B2)`

Result: 0; 14; 19; 0; 32; 58

## How it works

An invoice is late by the days between its due date and the day it was paid, or the as-of date in G1 if it's still open. IF picks the right end date, and `MAX(0, …)` turns invoices paid early or not yet due into 0 instead of a negative number.

## Other ways

- `=IF(C2="",MAX(0,$G$1-B2),MAX(0,C2-B2))`: The same logic written out for each case, if your team finds it easier to read.
- `=MAX(0,$G$1-B2)`: Ignores the paid date. Use it only on a list of open invoices. (Paid invoices keep counting.)

## Common mistakes

- Without MAX, invoices paid early or not yet due show negative overdue days, which pulls averages down.
- A blank paid date counts as 0, so `C2-B2` on an unpaid invoice gives a huge negative number. Test for "" first, as here.
- Keep the as-of date in one cell and lock it with $. Put `=TODAY()` there for a live report.

## Questions

**How do I count working days overdue?**

Use NETWORKDAYS from the day after the due date: `=MAX(0,NETWORKDAYS(B2+1,IF(C2="",$G$1,C2)))`.

**How do I flag invoices more than 30 days overdue?**

`=IF(D2>30,"Escalate","")`, or group them into aging buckets.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/days-overdue