Formatting Issues

How to Automatically Highlight Excel Dates Older Than Three Months

Maira Mehtab Sep 22, 2026 868 views

Question details

The user wants to use Excel conditional formatting to automatically highlight dates that are more than three months older than today's date.

Product: Excel
Device & OS: not provided
Scenario: Tracking aging data, identifying overdue tasks, or managing historical deadlines by color-coding older dates.
Observed behavior: Requires a dynamic formula-based conditional formatting rule that correctly identifies and highlights dates exceeding a three-month threshold from the current day.

Before you start

Ensure that the cells you are applying the formatting to contain actual Excel date values and are not formatted as text, as text dates will cause the formula to fail.

Solution 1Recommended

Use Conditional Formatting with the EDATE Function

This solution uses the EDATE and TODAY functions within a conditional formatting rule to dynamically calculate the date from three months ago and highlight values that are older.

The EDATE function returns a date that is a specified number of months before or after a given date. By combining it with TODAY(), Excel can continuously recalculate the dynamic three-month threshold every time the workbook is opened.

Select the date range

Highlight the specific cells or the entire column containing the dates you want to evaluate.

Open Conditional Formatting

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

Set the rule criteria

In the New Formatting Rule dialog box, select 'Format only cells that contain'.

Enter the evaluation formula

In the rule description section, set the dropdowns to 'Cell Value' and 'less than'. In the formula box next to it, type the formula: =EDATE(TODAY(),-3)

Apply formatting style

Click the Format button, go to the Fill tab, select the color you want to use to highlight the older dates, and click OK twice to apply the rule.

Understanding the logic: Because Excel stores dates as sequential serial numbers, 'less than' a specific date means it occurred earlier in time. =EDATE(TODAY(),-3) establishes the exact date three months prior to today.

Efficient Spreadsheet Management

Automate Date Tracking with WPS Spreadsheet

WPS Spreadsheet features robust conditional formatting tools that are highly compatible with standard Excel formulas, allowing you to seamlessly track deadlines and highlight aging dates.

1. Open your data: Launch WPS Spreadsheet and open the document containing your date lists.
2. Select target cells: Highlight the range of cells where the dates are located.
3. Create a new rule: Go to the Home tab, click Conditional Formatting, and choose New Rule.
4. Apply the formula: Choose 'Format only cells that contain', set the condition to 'Cell Value less than', and enter the formula =EDATE(TODAY(),-3).
5. Customize and confirm: Click Format to choose a highlight color, then click OK to automatically identify dates older than three months.

Fully compatible with Microsoft Excel (.xlsx, .xls) formats and formulas.Familiar, easy-to-use Conditional Formatting interface.Free and lightweight office alternative with powerful data visualization tools.

microsoft office alternative - wps office

Frequently Asked Questions

Why isn't my conditional formatting rule highlighting the correct dates?

This most commonly occurs when dates are stored as text rather than numerical date values. You can resolve this by selecting the date column, navigating to Data > Text to Columns, and clicking Finish to convert them into standard serial dates recognized by formulas.

How can I change the rule to highlight dates older than 6 months?

You can modify the EDATE formula by changing the second argument, which represents the number of months. Use =EDATE(TODAY(),-6) in your conditional formatting rule to target dates older than six months.

Can I highlight dates that are approaching a deadline instead of older dates?

Yes. If you want to highlight upcoming dates (e.g., within the next 30 days), you can create a rule using a formula instead of a cell value. Select 'Use a formula to determine which cells to format' and use =AND(A1>=TODAY(), A1<=TODAY()+30), assuming A1 is your starting cell.

How do I remove the conditional formatting rule if I no longer need it?

To remove the highlighting, go to the Home tab, click Conditional Formatting, select Clear Rules, and choose either 'Clear Rules from Selected Cells' or 'Clear Rules from Entire Sheet'.

Document & File Issues