Formatting Issues

How to Highlight Dates Older Than Two Years in Excel Using Conditional Formatting

Amos Gikunda Oct 9, 2026 869 views

Question details

The user needs to apply an Excel conditional formatting rule to highlight training dates that are more than two years old, while ensuring that any blank cells are not formatted.

How to Highlight Dates Older Than Two Years in Excel

Product: Excel
Device & OS: not provided
Scenario: Tracking expiration dates, training logs, or past due events based on a dynamic two-year timeframe from the current date.
Observed behavior: Dates older than two years are automatically highlighted, blank cells remain completely unformatted, and the calculation correctly accounts for leap years.

Before you start

Ensure your date column contains valid Excel date formats rather than text strings, and select the specific range you want to format before applying the new rule.

Solution 1Recommended

Use the EDATE and TODAY Functions in a Custom Formatting Rule

Applying a custom formula rule using EDATE and TODAY ensures precise calculation of a two-year gap (accounting for leap years), while an IF statement safely skips blank cells.

By default, Excel treats blank cells as zero, which equates to January 0, 1900. Without an exclusion, conditional formatting will treat blank cells as being older than two years and highlight them. The IF function solves this.

Select the target range

Highlight the range of cells containing your dates. For example, click and drag to select A1:A100 (or whichever column contains your training dates).

Open the Conditional Formatting menu

Go to the 'Home' tab on the Excel ribbon, click on 'Conditional Formatting' in the Styles group, and select 'New Rule' from the drop-down menu.

Choose the formula rule type

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 conditional formula

In the formula box, type: `=IF(A1="",FALSE,TODAY()>EDATE(A1,12*2))`. Note: Ensure that 'A1' matches the topmost cell of your selected range.

Set the highlight format

Click the 'Format' button, navigate to the 'Fill' tab, select your preferred highlight color (e.g., red or yellow), and click 'OK' twice to apply the rule.

Use the EDATE and TODAY Functions in a Custom Formatting Rule

Adjusting the Timeframe: To highlight dates older than three years instead, simply change the `12*2` portion of the formula to `12*3`. The EDATE function multiplies 12 months by the number of years.

Data Visualization

Apply Conditional Formatting Easily in WPS Spreadsheet

WPS Spreadsheet fully supports complex conditional formatting rules, including custom formulas like EDATE and TODAY. You can seamlessly track dates, manage training logs, and highlight critical data for free.

1. Select your data in WPS: Open your document in WPS Spreadsheet and highlight the column or range of dates you want to format.
2. Access Conditional Formatting: Navigate to the 'Home' tab and click the 'Conditional Formatting' icon.
3. Create a New Rule: Select 'New Rule' from the dropdown, then choose 'Use a formula to determine which cells to format'.
4. Apply formula and style: Input `=IF(A1="",FALSE,TODAY()>EDATE(A1,24))`, click 'Format' to choose your fill color, and hit 'OK'.

100% compatible with Microsoft Excel conditional formatting rules and formulasUser-friendly interface for managing multiple formatting rules simultaneouslyFree and lightweight alternative for processing large datasets efficientlyBuilt-in templates for training logs and schedule tracking

microsoft office alternative - wps office

Frequently Asked Questions

Why are my blank cells getting highlighted when formatting for old dates?

Blank cells evaluate to 0, which Excel interprets as January 0, 1900. Since 1900 is much older than two years, Excel highlights it. Using the `=IF(A1="",FALSE,...)` condition prevents blank cells from triggering the format.

Does the EDATE function accurately handle leap years?

Yes, EDATE calculates dates by adding or subtracting exact months. When evaluating a two-year span using 12*2 (24 months), it inherently accounts for leap years and different month lengths, ensuring an exact date match.

How can I change the formula to highlight dates older than exactly 90 days instead?

If you want to use a specific number of days instead of calendar months, you can bypass EDATE and use a simple subtraction formula: `=IF(A1="",FALSE,TODAY()-A1>90)`.

Document & File Issues