Highlight Overdue and Upcoming Dates in Excel

To highlight overdue dates in Excel, add a conditional formatting rule that compares each due date with TODAY(). Anything earlier than today turns red, and because TODAY updates daily, the colours do too. This guide builds a task tracker step by step: overdue in red, due soon in amber, finished tasks left alone, and a count of what's late.

How to highlight overdue dates step by step#

Use a formula rule with TODAY. Here's the tracker the examples use, with due dates in column C:

To colour overdue rows:

1. Select A2:D7, starting from A2.
2. Go to Home > Conditional Formatting > New Rule.
3. Choose Use a formula to determine which cells to format.
4. Enter =$C2<TODAY().
5. Click Format, pick a light red fill on the Fill tab, and click OK twice.

If today is 10 June 2024, rows 2, 3 and 5 turn red. The Board pack, due today, does not: it isn't late yet.

The $ before C locks the column, so every cell in a row checks that row's due date and the whole row is coloured. The row number has no $, so it moves down row by row. The guide to highlighting a whole row explains this in more depth. The TODAY function guide covers how TODAY recalculates.

How to skip blank cells and finished tasks#

Add extra tests with AND so the rule only colours rows that really need attention. The basic rule has two problems.

First, empty due dates turn red. Excel treats an empty cell as 0, and 0 is earlier than any real date. Second, finished tasks like Update CRM stay red forever. Fix both in one formula:

=AND($C2<>"",$D2<>"Done",$C2<TODAY())

AND returns TRUE only when all three tests pass: there's a due date, the task isn't Done, and the date is in the past. Now only Send invoice and Order stock are red.

How to highlight dates due in the next 7 days#

Add a second rule for upcoming deadlines with a softer colour, such as amber. Select the same range and use:

=AND($D2<>"Done",$C2>=TODAY(),$C2<=TODAY()+7)

This colours tasks due from today up to 7 days ahead. On 10 June, Board pack (due today) and Team review (due on 12 June) turn amber. Renew licence, due on 20 June, stays plain.

Change 7 to any window you like, such as 3 or 30 days. To keep the window easy to adjust, type the number of days in a cell such as H1 and use TODAY()+$H$1 in the rule. Anyone can then change the warning period without opening the rule editor. To flag only items due today, use =$C2=TODAY(). For the maths behind these windows, see how to count days between dates.

Why is the built-in date rule not enough?#

Excel's ready-made rule doesn't have an "earlier than today" option. Under Home > Conditional Formatting > Highlight Cells Rules > A Date Occurring, you can choose fixed periods like Yesterday, Today, Tomorrow, In the last 7 days, Next week or Last month.

These are handy for quick views, such as "this week's deliveries". But a task that was due three weeks ago doesn't match any of them, so it wouldn't be flagged. That's why overdue tracking needs a formula rule with TODAY.

How to manage several rules on one range#

When rules overlap, the order decides which colour wins. Open Home > Conditional Formatting > Manage Rules and choose This Worksheet to see them all.

• Rules higher in the list are applied first. Use the arrow buttons to move the overdue rule to the top.
• Tick Stop If True on a rule if you don't want later rules to apply when it matches.
• Check Applies to when the table grows. Extend it, for example to =$A$2:$D$200, so new rows are covered.

In this tracker the rules don't clash, because a date can't be both before today and on or after today. Ordering matters more when you add other rules, such as one for highlighting duplicate entries or highlighting weekends.

How to count and label overdue items#

Colours help people scan, but a number is better for a summary. COUNTIFS counts rows that meet several conditions. Join the < operator to TODAY with &:

=COUNTIFS(C2:C7,"<"&TODAY(),D2:D7,"<>Done")

On 10 June 2024, the result is 2. Writing "<TODAY()" without the & would not work, because inside quotes TODAY() is just text.

To add a status column instead of, or as well as, colours, use IF:

=IF(D2="Done","Done",IF(C2<TODAY(),"Overdue","Open"))

The guide to IF with dates shows more ways to compare dates in formulas.

Common mistakes with overdue rules#

Most broken rules come from references or from blank cells. Check these:

1. Formula written for the wrong cell. The rule must point at the first row of the selection, here row 2.
2. Missing $ on the column. Without $C2, the other columns check the wrong cells and the colours scatter.
3. Blank due dates colour red. Add $C2<>"".
4. Text dates. A date stored as text is never "less than" TODAY, so it's never flagged. Convert it to a real date.
5. Using a fixed date. A rule like =$C2<DATE(2024,6,10) never updates. Use TODAY().

Practise it#

Flagging overdue work is one of the most practical Excel skills, and it shows up in admin and operations job tests. Practise conditional formatting in a live spreadsheet, then practise using dates in criteria with COUNTIFS and SUMIFS. Each lesson checks your work instantly. Or try the exercise on this page first.