Formatting Issues

Highlight Rows When a Date Is 10 Days Overdue in Excel

Maira MehtabMaira Mehtab Nov 18, 2026 869 views

Question details

Highlight each row when its date in column D is at least 10 days before today.

Highlight Rows When a Date Is 10 Days Overdue in Excel
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.
Before you start

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.

Solution 1Recommended

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.

1
Select the rows

Select A2:I18, or select the equivalent range in your worksheet without including the header row.

2
Create a rule

On the Home tab, select Conditional Formatting > New Rule, then choose Use a formula to determine which cells to format.

3
Enter the formula

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.

4
Choose the highlight

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.

Create a Formula-Based Conditional Formatting Rule in Excel
How the reference works: The dollar sign locks column D while the row number changes for each row. If every cell in the date column contains a valid date, the shorter formula =$D2<=TODAY()-10 also works.
WPS Spreadsheets

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. 1. Open the workbook: Open WPS Spreadsheets and use Open to select your Excel workbook.
  2. 2. Select the data: Select the range to highlight, such as A2:I18, excluding the header row.
  3. 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. 4. Set the format: Choose a fill color, confirm the rule, and check its applied range in Conditional Formatting > Manage Rules.
Highlight complete rows based on dates in one column.Ignore blank and text cells with an ISNUMBER check.Open and edit Microsoft Excel XLSX and XLS files.Use a familiar spreadsheet interface.
QA img-9

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.