Deadlines slip when they’re buried in a spreadsheet. Conditional formatting makes overdue items and approaching deadlines impossible to miss — red for past due, yellow for due soon, green for on track. No manual checking, no missed dates. The formatting updates automatically every time you open the file.
The Data
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Task | Assigned To | Due Date | Status |
| 2 | Invoice #4021 | Sarah | 3/28/2025 | Open |
| 3 | Quarterly Report | James | 4/10/2025 | Open |
| 4 | Client Proposal | Maria | 4/25/2025 | Open |
| 5 | Budget Review | David | 4/15/2025 | Complete |
Rule 1: Highlight Overdue Items (Red)
1Select the data range (e.g., A2:D100)
2Go to Home → Conditional Formatting → New Rule
3Select “Use a formula to determine which cells to format”
4Enter:
=AND($C2"Complete")
Highlights the entire row red when the due date is past AND the status isn’t “Complete”. The dollar sign on $C locks the column reference so it works across the row.
Set the format to a light red fill with dark red text.
Rule 2: Highlight Approaching Deadlines (Yellow)
Same steps, but with this formula:
=AND($C2>=TODAY(), $C2<=TODAY()+7, $D2<>"Complete")
Highlights rows where the due date is within the next 7 days and the task isn’t complete. Change 7 to any number of days for your warning window.
Rule 3: Highlight On-Track Items (Green)
=OR($D2="Complete", $C2>TODAY()+7)
Green for completed items or items with more than 7 days remaining.
Rule priority matters
In the Conditional Formatting Rules Manager, make sure Overdue (red) is at the top, then Approaching (yellow), then On Track (green). Excel applies rules top-down, so the first matching rule wins. Check “Stop If True” on each rule to prevent overlap.
Adding a Days Remaining Column
For a numeric countdown alongside the color coding:
=IF(D2="Complete", "Done", IF(C2
TODAY() recalculates
TODAY() updates every time the workbook opens or recalculates. This means your conditional formatting is always current — but it also means printing the same file on different days will show different highlights. If you need a fixed snapshot, paste the date as a static value.
When Formulas Aren't Enough
Conditional formatting highlights problems visually, but it can't take action on them:
- Email notifications — automatically alerting the assigned person when their task is approaching or overdue
- Dashboard rollup — a summary showing how many tasks are overdue, approaching, and on track across all departments
- Escalation rules — if overdue by 3+ days, flag it to the manager; if 7+ days, escalate to the director
- Recurring deadline tracking — monthly tasks that auto-generate new deadlines when completed
- Multi-workbook consolidation — tracking deadlines across project files from different teams
A custom VBA deadline tracker monitors dates, sends alerts, builds dashboards, and manages escalation — keeping nothing from slipping through the cracks.
"Outstanding Service! Helped us while on his vacation! My Excel Guy! 10 Stars"
Darryl H. · Repeat client · PeoplePerHour
Need Automated Deadline Tracking with Alerts?
Tell us about your workflow and we'll build a tool that keeps everything on schedule.
Start a Project