Formatting Issues

How to Highlight Excel Dates Red Three Months After Due Date

Kushani Nimanthika Oct 10, 2026 869 views

Question details

The user needs to automatically highlight cells in an Excel date column in red if the dates are at least three months older than the current date.

How to Highlight Excel Dates Red Three Months After Due Date

Product: Excel
Device & OS: not provided
Scenario: Managing aging reports, subscriptions, or overdue invoices where dates older than three months require immediate visual emphasis.
Observed behavior: Dates need to dynamically evaluate against the current date and display a red background when they meet the three-month aging condition.

Before you start

Ensure that the cells in your column are formatted as valid Excel date values rather than text strings, as the formula relies on numerical dates to calculate the time difference accurately.

Solution 1Recommended

Use Conditional Formatting with the EDATE Function

Apply a conditional formatting rule using the EDATE and TODAY functions to dynamically color cells red when they are older than three months.

The EDATE function calculates a date a specific number of months before or after a starting date. Combined with the TODAY() function, it evaluates the exact threshold for 'three months ago' regardless of when you open the document.

Select the date column

Highlight the entire column or the specific range of cells 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 drop-down menu.

Set the Rule Type

In the New Formatting Rule dialog box, choose the option that says 'Format only cells that contain'.

Configure the Condition

Leave the first drop-down as 'Cell Value'. Change the second drop-down to 'less than or equal to'. In the formula text box, type: =EDATE(TODAY(),-3).

Apply the Red Fill

Click the 'Format' button, navigate to the 'Fill' tab, select a red background color, and click 'OK' twice to apply the rule.

Use Conditional Formatting with the EDATE Function

Dynamic Updates: Because the rule uses the TODAY() function, the formatting will automatically recalculate and update every time you open or refresh the workbook.

WPS Spreadsheet Solution

Highlight Overdue Dates Easily in WPS Spreadsheet

WPS Spreadsheet fully supports advanced conditional formatting rules and dynamic date formulas like EDATE and TODAY. You can easily manage aging reports and highlight overdue data in a familiar interface.

1. Open your document: Launch WPS Spreadsheet and open the workbook containing your dates.
2. Access Conditional Formatting: Select the date cells, go to the Home tab, and click Conditional Formatting > New Rule.
3. Apply the Formula: Select 'Format only cells that contain', set the condition to 'less than or equal to', input =EDATE(TODAY(),-3), set the fill color to red, and click OK.

Fully compatible with Microsoft Excel (.xlsx) conditional formatting rules.Lightweight, fast, and completely free to use for data analysis.Seamlessly evaluate complex date criteria with built-in advanced functions.

microsoft office alternative - wps office

Frequently Asked Questions

Why isn't the conditional formatting working on my dates?

This usually happens if your dates are formatted as text instead of true date values. You can fix this by selecting the column, navigating to the Data tab, clicking 'Text to Columns', and immediately clicking 'Finish' to convert them.

Can I modify the rule to highlight dates older than 6 months?

Yes, you can easily adjust the EDATE formula. Simply change the rule from =EDATE(TODAY(),-3) to =EDATE(TODAY(),-6) in the Conditional Formatting Rules Manager.

How do I highlight dates that are exactly three months from today?

To highlight a precise date rather than a range, change the formatting rule condition from 'less than or equal to' to 'equal to', while keeping the same =EDATE(TODAY(),-3) formula.

Document & File Issues