To highlight overdue dates in Excel, create a conditional formatting rule with a formula that compares each date to today.
The key detail is deciding whether a task due today counts as overdue—and preventing blank cells from being highlighted by mistake.
Formula to highlight overdue dates
Suppose due dates are in cells B2:B100, with one date per row.
Select that range, then create a conditional formatting rule that uses this formula:
=AND(B2<TODAY(),B2<>"")This rule highlights dates earlier than today and ignores empty cells.
The formula uses B2, the top-left cell in the selected range.
Excel adjusts the reference for each cell, so the rule checks B3 for the next row, B4 after that, and so on.
The comparison B2<TODAY() means strictly earlier than today.
A date due today is not overdue under this definition.
The second condition, B2<>"", excludes blanks explicitly.
Set up the conditional formatting rule
- Select the cells containing due dates, such as
B2:B100.
Avoid including the header. - On the Home tab, select Conditional Formatting, then New Rule.
- Choose Use a formula to determine which cells to format.
- Enter
=AND(B2<TODAY(),B2<>"")in the formula box. - Choose Format, select a fill color or font style, and confirm the rule.
In the rule manager, check that Applies to is =$B$2:$B$100.
The range is absolute, while the formula reference B2 is relative.
This lets Excel apply the same logic to every selected cell.
Choose whether today counts as overdue
Teams do not always define “overdue” the same way.
If a task should be flagged on its due date, use a less-than-or-equal comparison:
=AND(B2<=TODAY(),B2<>"")For example, if today is 15 May and the due date is 15 May, the original formula leaves the cell unformatted.
The revised formula highlights it.
A due date of 14 May is highlighted by both formulas; a date of 16 May is highlighted by neither.
Choose one rule based on your process: use <TODAY() when a deadline becomes overdue the day after its due date, and <=TODAY() when it must be completed by the start of its due date.
Highlight an entire row based on its due date
To highlight a task’s full row when its due date in column B has passed, select the data area, for example A2:F100.
Create a formula-based rule using:
=AND($B2<TODAY(),$B2<>"")The dollar sign locks the column to B, so Excel always checks the due-date column.
The row number stays relative, allowing row 2’s rule to check B2, row 3’s to check B3, and so on.
Set Applies to to =$A$2:$F$100 and choose the desired format.
Use a status column to skip completed tasks
If column C contains a status such as Complete or Open, you can highlight overdue dates only for unfinished tasks.
For a range starting on row 2, use:
=AND($B2<TODAY(),$B2<>"",$C2<>"Complete")This formula checks the date in column B, ignores blank due dates, and excludes rows whose status is Complete.
For a full-row rule, apply it to the task table, such as A2:F100.
If your workbook uses another completion label, replace Complete with the exact text used in the status column.
Common issues and edge cases
Blank cells are still highlighted
Excel can treat a blank cell like a zero in date comparisons.
Include the nonblank check B2<>"" in the rule.
If cells contain formulas that return an empty string, this check also normally prevents those results from being flagged.
Dates stored as text do not behave as expected
A value that looks like a date may be stored as text, especially after importing a CSV or copying data from another system.
Date comparisons may then fail or act inconsistently.
Convert the entries to real Excel dates—for example, with Text to Columns or a suitable date-conversion formula—and confirm the cell’s number format is a date.
Formatting alone does not convert text into a date value.
The rule highlights the wrong cells
Make sure the formula’s row number matches the first row of the Applies to range.
For B2:B100, start with B2.
For B5:B100, start with B5.
A mismatch can shift the check by one or more rows.
Dates include times
Excel stores a date and time together as a number with a fractional time component.
If your rule uses <TODAY(), a value for today at 9:00 a.m. is greater than today’s midnight and will not be flagged.
To flag anything before the current moment, compare with NOW() instead:
=AND(B2<NOW(),B2<>"")Use NOW() only when the time of day matters.
For date-based deadlines, compare the date portion while guarding against blanks: =IF($B2="",FALSE,INT($B2)<TODAY()).
This treats any time on an earlier calendar date as overdue while leaving today’s dates unflagged.
Compatibility and recalculation
AND and TODAY are available in current desktop versions of Excel, Excel for the web, and older versions that support conditional formatting formulas.
The basic rules do not require Microsoft 365 or dynamic array functions.
TODAY() updates when Excel recalculates the workbook, so a date can become overdue without editing the cell.
If the highlight does not update, check Formulas > Calculation Options and confirm calculation is set to Automatic.
In manual calculation mode, recalculate the workbook to refresh date-based formatting.