Highlight Rows When a Date Is 10 Days Overdue in Excel
Question details
Highlight each row when its date in column D is at least 10 days before today.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A worksheet contains dates in column D, and the user wants to highlight the corresponding rows when those dates are overdue by at least 10 days.
- Observed behavior
- Rows should receive the chosen formatting when the date in column D is 10 or more days in the past.
Check that the values in column D are actual Excel dates rather than text. Identify the rows and columns to highlight; the example below uses A2:I18, with data starting in row 2.
Create a Formula-Based Conditional Formatting Rule in Excel
Use this rule to highlight the entire selected row while ignoring blank or text cells in the date column.
Select A2:I18, or select the equivalent range in your worksheet without including the header row.
On the Home tab, select Conditional Formatting > New Rule, then choose Use a formula to determine which cells to format.
Enter =AND(ISNUMBER($D2),$D2<=TODAY()-10). Change 2 to the first row number of your selected range and D to your date column if needed.
Select Format, choose a fill color, and select OK. Confirm the rule, then check Conditional Formatting > Manage Rules to verify that Applies to covers the intended range, such as =$A$2:$I$18.

Highlight Overdue Date Rows in WPS Spreadsheets
Use a formula-based conditional formatting rule in WPS Spreadsheets to highlight rows whose dates are at least 10 days overdue.
- 1. Open the workbook: Open WPS Spreadsheets and use Open to select your Excel workbook.
- 2. Select the data: Select the range to highlight, such as A2:I18, excluding the header row.
- 3. Add the formula rule: Go to Home > Conditional Formatting > New Rule, choose the formula-based rule option, and enter =AND(ISNUMBER($D2),$D2<=TODAY()-10).
- 4. Set the format: Choose a fill color, confirm the rule, and check its applied range in Conditional Formatting > Manage Rules.

Frequently Asked Questions
Why are blank date cells being highlighted?
The shorter rule =$D2<=TODAY()-10 can treat a blank cell as zero. Edit the rule under Home > Conditional Formatting > Manage Rules and use =AND(ISNUMBER($D2),$D2<=TODAY()-10) to exclude blanks and text.
How do I use a different date column?
Replace D in both references with the correct column letter. For example, use =AND(ISNUMBER($F2),$F2<=TODAY()-10) for dates in column F, adjusting the row number to match the first row of your selected range.
Why is only the date cell highlighted instead of the whole row?
Open Home > Conditional Formatting > Manage Rules and expand the Applies to range to include all the desired columns, such as =$A$2:$I$18. Keep the date column locked in the formula with $D2.
Does the highlighting update as the date changes?
Yes. TODAY() uses the current date when the workbook recalculates, so qualifying rows can change over time. If the formatting does not update, check Excel's calculation settings and recalculate the workbook.




