Overdue and unpaid
PremiumHighlight the invoice when it is overdue and unpaid. In D2, write the rule with AND. The due date in B2 must be before G1, and C2 must say "No". Lock the columns and all of G1.
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Invoice | Due | Paid | Rule | Today | Nov 2, 2026 | ||
| 2 | 1043 | Oct 27, 2026 | No | |||||
| 3 | ||||||||
| 4 | ||||||||
| 5 | ||||||||
| 6 | ||||||||
| 7 | ||||||||
| 8 |
Premium lesson
Unlock Conditional formatting and every other lesson
This is a premium lesson. Premium lets you solve it and every other lesson, take the timed mock tests, and earn certificates you can share on LinkedIn.
Why this works
AND returns TRUE only when both tests are TRUE. Invoice 1043 was due Oct 27, before Nov 2 in G1, and C2 says No. Both tests are TRUE, so the row is highlighted.
When the invoice is paid and someone types Yes in C2, the second test turns FALSE and the color goes away on its own.
Common mistake: Using OR, so paid invoices turn red too as long as they are old enough.
The answer
- D2
=AND(1$B2<$G$1,2$C2="No")
In plain words: 1The due date in B2 is before today in G1, 2and C2 says No.