Function Problems

How to Highlight Dates Older Than 4 Days in Excel

Amos Gikunda Sep 28, 2026 869 views

Question details

The user wants to set up a conditional formatting rule to highlight cells containing dates that are at least four days older than the current date, while ensuring that blank cells are ignored and not highlighted.

How to Highlight Dates Older Than Four Days in Excel

Product: Microsoft Excel
Device & OS: not provided
Scenario: Applying conditional formatting to a date column to track aging tasks, overdue invoices, or older records without falsely flagging empty rows.
Observed behavior: A standard less-than-today formula often highlights blank cells by mistake. The goal is to dynamically format based on a rolling date (4 days before today) while successfully bypassing empty cells.

Before you start

Ensure your target cells are properly formatted as 'Date' rather than 'Text'. Select the entire range you want to apply the rule to before entering the formula, and note the address of the very first cell in your selection.

Solution 1Recommended

Use Conditional Formatting with AND, ISNUMBER, and TODAY Functions

The most reliable method to highlight older dates while excluding blanks is to combine the AND, ISNUMBER, and TODAY functions into a custom formatting rule.

In Excel, blank cells are evaluated as the number zero, which corresponds to the date January 0, 1900. Because this default date is always less than today's date, standard subtraction formulas will incorrectly highlight empty cells. By introducing the ISNUMBER function, you force Excel to verify that the cell contains an actual value before applying the format.

Select your data range

Highlight the range of cells containing the dates you want to evaluate. For example, select cells C2 through C100. Make sure you remember the topmost cell in this selection (e.g., C2).

Open Conditional Formatting menu

Navigate to the 'Home' tab on the Excel ribbon, click on 'Conditional Formatting' in the Styles group, and select 'New Rule' from the dropdown menu.

Choose the formula option

In the New Formatting Rule dialog box, select 'Use a formula to determine which cells to format' from the list of rule types.

Enter the custom formula

In the formula bar, type `=AND(ISNUMBER(C2),C2<TODAY()-4)`. If your selected range starts at a different cell, such as A1, replace both instances of 'C2' with 'A1'.

Set the highlight format

Click the 'Format' button, go to the 'Fill' tab, choose a background color (like red or yellow) to highlight the older dates, and click 'OK' twice to apply the rule.

Use Conditional Formatting with AND, ISNUMBER, and TODAY Functions

Dynamic Updates: Because this formula uses the TODAY() function, the highlighting will automatically update every time you open the workbook or whenever the date changes.

Track Dates Easily with WPS Spreadsheet

Highlight Aging Dates Instantly in WPS Office

WPS Spreadsheet fully supports advanced conditional formatting formulas, including TODAY and ISNUMBER. It allows you to efficiently track older dates, manage overdue tasks, and organize large datasets with an intuitive interface and zero hassle.

1. Open your spreadsheet: Launch WPS Spreadsheet and open the document containing your date records.
2. Select the target cells: Highlight the column or range of dates you wish to evaluate, noting the very first cell in your selection (e.g., C2).
3. Access Conditional Formatting: Go to the 'Home' tab, click on 'Conditional Formatting', and select 'New Rule'.
4. Apply the rule: Select the formula option, enter `=AND(ISNUMBER(C2),C2<TODAY()-4)`, choose your preferred highlight color under the Format settings, and click OK.

Fully compatible with Microsoft Excel conditional formatting rules and formulasLightweight application that runs smoothly even on older devicesIntuitive interface for managing complex data tracking and customized rulesFree to use for everyday data analysis and office tasks

microsoft office alternative - wps office

Frequently Asked Questions

Why does a simple C2<TODAY()-4 formula highlight blank cells?

Excel stores dates as sequential serial numbers starting from 1 for January 1, 1900. Blank cells are treated as 0. Since 0 is always mathematically less than today's serial number minus 4, Excel highlights the blank cells. Using the ISNUMBER function forces Excel to ignore these blanks.

How do I change the formula to highlight dates exactly 4 days old?

To highlight dates that are exactly four days old, change the less-than sign to an equals sign in your formula. The updated formula will be: `=AND(ISNUMBER(C2), C2=TODAY()-4)`.

Can I highlight dates that are within the last 4 days instead of older than 4 days?

Yes. To highlight dates that fall between today and four days ago, you can adjust the logic to check if the date is greater than or equal to 4 days ago, but less than or equal to today. Use this formula: `=AND(ISNUMBER(C2), C2>=TODAY()-4, C2<=TODAY())`.

How do I apply this rule to an entire row based on the date cell?

To highlight the entire row, select your entire data table (e.g., A2:F100) before creating the rule. Then, lock the column reference for the date cell using a dollar sign in the formula. If your dates are in column C, use: `=AND(ISNUMBER($C2),$C2<TODAY()-4)`.

Excel & Data Problems