Overdue deadlines
PremiumHighlight the row when the deadline has passed. Today's date is in G1. In D2, write the rule that tests whether the deadline in C2 is before G1. Lock column C and all of G1 with $.
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Task | Owner | Deadline | Rule | Today | Nov 2, 2026 | ||
| 2 | Sales tax filing | Sara | Oct 27, 2026 | |||||
| 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
Dates are numbers in Excel, so "before" just means less than. The deadline Oct 27 is less than Nov 2 in G1, so the test returns TRUE and the row is highlighted.
In your own workbook you can type `TODAY()` instead of $G$1. Then the rule always uses today's date, and a deadline turns red the day it passes.
Common mistake: Typing G1 without $. The rule moves to the other cells, and they test against empty cells.
The answer
- D2
=$C2<$G$1