Make deadlines impossible to miss. Conditional-formatting rules using TODAY() turn past-due dates red and upcoming ones amber — and they re-evaluate every day automatically.
B2 <= TODAY()+7.
The example
Due dates relative to today (June 17, 2026): past = overdue, next 7 days = due soon.
| A | B | |
|---|---|---|
| 1 | Task | Due |
| 2 | Invoice | 6/10/2026 (overdue) |
| 3 | Report | 6/20/2026 (due soon) |
| 4 | Audit | 7/30/2026 |
The formula
Two rules — overdue first, then due-soon:
How it works
Layer two formula rules and let order do the work:
- Add the overdue rule first:
=B2 < TODAY()with a red fill. Anything before today turns red. - Add the due-soon rule:
=AND(B2>=TODAY(), B2<=TODAY()+7)with amber, catching the next 7 days. - Because the overdue rule sits above due-soon in Manage Rules, an already-past date never gets mis-flagged as “soon.”
- TODAY() recalculates each day the file opens, so the highlights roll forward automatically — no manual updating.
Skip completed tasks: add a status check so done items don’t glow red — =AND($C2<>"Done", $B2<TODAY()). Lock the columns with $ and select the whole table to shade entire rows.
Try it: interactive demo
Set a due date; see its status vs today.
Variations
Skip completed
Don’t flag done tasks:
Due today exactly
Highlight just today:
Within 30 days
A wider window:
Pitfalls & errors
Rule order matters. Put the overdue rule above due-soon (or stop if true), or an overdue date can pick up the wrong color.
TODAY() is volatile. It updates on recalc, so the file’s highlights change day to day — intended, but it marks the workbook as modified.
Times sneak in. If a cell holds a date and time, =B2<TODAY() treats today’s afternoon as “past.” Use INT(B2) to compare dates only.
Practice workbook
Frequently asked questions
How do I highlight overdue dates in Excel?
How do I highlight dates due within a week?
How do I stop completed tasks from being highlighted?
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