Certifications, contracts, and warranties all expire. A conditional-formatting rule paints anything due in the next 30 days so nothing slips through.
Set a yellow or amber fill; rows expiring within 30 days light up automatically.
The example
A list of certifications with expiry dates. Anything due within 30 days of today should stand out.
| A | B | |
|---|---|---|
| 1 | Item | Expires |
| 2 | Forklift cert | in 12 days |
| 3 | First aid | in 90 days |
| 4 | License | in 5 days |
The formula
The rule tests two things at once with AND: the date is still in the future, and it is no more than 30 days away:
How it works
Setting it up:
- Select the data column (for example A2:A100), then Home → Conditional Formatting → New Rule → Use a formula.
- Enter the formula referencing the first cell of the selection (A2) with no row lock.
- TODAY() recalculates every day, so the highlight window slides forward automatically.
- Pick a fill colour and click OK; the rule applies to every selected row relative to its own date.
Add a second rule with =A2<TODAY() in red to flag items that have already expired.
Try it: interactive demo
Enter how many days until an item expires; see whether the 30-day rule highlights it.
Variations
Expiring this week (7 days)
Tighten the window for a weekly review.
Already expired
A separate red rule for past dates.
Pitfalls & errors
Lock the column but not the row ($A2) if you want to colour the entire row instead of just the date cell.
Order matters: put the "expired" red rule above the "due soon" amber rule, or check Stop If True.
Practice workbook
Frequently asked questions
Will the colours update by themselves?
Can I colour the whole row, not just the date?
Why is nothing highlighting?
Stop fighting formulas. Learn them in a day.
This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class