Flagging overdue invoices
Build an overdue flag from IF, AND and one report date, then count the overdue invoices and total what they are worth.
Task 1Is the due date in the past?3 questions
An invoice is overdue when its due date has passed and it has not been paid. Start with the date.
A sheet can compare dates. A later date counts as bigger, so =D3<$B$1 is TRUE when the due date is before the report date.
The report date sits in one cell, B1, locked with dollar signs so that the test can be copied down. An invoice due on the report date is not overdue yet.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Report date | 20 Mar 2026 | ||||
| 2 | Invoice | Customer | Amount | Due date | Paid | Status |
| 3 | 2101 | Quay Dental | 240.00 | 6 Mar 2026 | Yes | |
| 4 | 2102 | Hollin School | 412.50 | 13 Mar 2026 | No | |
| 5 | 2103 | Moss Lane Vets | 95.00 | 20 Mar 2026 | No | |
| 6 | 2104 | Finch Studio | 130.25 | 27 Mar 2026 | No | |
| 7 | 2105 | Quay Dental | 320.00 | 10 Mar 2026 | No |
Task 2Flag it only if it is unpaid3 questions
Task 3Count and total the overdue invoices3 questions