Highlight overdue and upcoming dates in Excel

Create the overdue rule

A formula rule is written for the first cell of the selection and Excel adjusts it for every other cell, the same way a formula fills down. That is why the rule says B2 even though it colors B2 to B9.

TODAY() updates whenever the workbook recalculates, so the colors move on by themselves each morning. In the formula results below, cell F2 stands in for TODAY() so the examples stay fixed at November 2, 2026.

1. Select B2:B9, starting from B2 so it is the active cell.
2. Home > Conditional Formatting > New Rule.
3. Choose "Use a formula to determine which cells to format".
4. Type =AND(B2<>"",B2<TODAY()).
5. Click Format, pick a red fill on the Fill tab, then OK twice.

Why blank dates turn red

The obvious rule =B2<TODAY() colors empty cells too. Excel treats an empty cell as 0 in a comparison, and 0 is earlier than any real date. The Website copy task has no due date yet, but the simple rule flags it as overdue.

Adding B2<>"" inside AND fixes this. Check it on row 6: the result is FALSE, so the empty cell stays white.

Add a "due in the next 7 days" rule

Select B2:B9 again and add a second formula rule with an amber or yellow fill. It catches today and the next seven days. Change 7 to 14 or 30 for a longer warning window.

Today's date itself falls in this rule, not the overdue rule, because the overdue rule uses "less than today". The Supplier call on November 9 is exactly 7 days away, so it is included.

Ignore tasks that are done

A paid invoice or a finished task should not stay red. Add the status test to the rule and point it at column C with a $ before the column letter. In the real rule you would write =AND($B2<>"",$B2<TODAY(),$C2<>"Done").

Send Q3 invoices was due October 31 but is marked Done, so it stays white. Renew insurance is still Open, so it turns red.

Color the whole row, not just the date

Select A2:D9 instead of B2:B9, and lock the column in the rule: =AND($B2<>"",$B2<TODAY(),$C2<>"Done"). The $ keeps every cell in the row looking at column B and C, while the row number still moves down. Without the $, the references shift with each column: cell C2 would test D2 and E2 instead of B2 and C2, and the colors would scatter.

If two rules both apply, the one higher in Home > Conditional Formatting > Manage Rules wins for any format they both set. Keep the red overdue rule above the amber one.

The quick built-in option, and counting the red cells

Home > Conditional Formatting > Highlight Cells Rules > A Date Occurring offers Yesterday, Today, Tomorrow, In the last 7 days, Last week, This week, Next week, Last month, This month and Next month. It needs no formula, but it has no "overdue" or "next 30 days" choice, so most trackers need the formula rules above.

Conditional formatting cannot be counted directly. Count with the same logic instead, using COUNTIFS. It skips blank dates on its own, because an empty cell is not a date.