Function Problems

How to Highlight Renewal Dates 3 Months in Advance in Excel

Guest Writer Oct 12, 2026 869 views

Question details

The user wants to apply conditional formatting to a column of renewal dates so that dates falling within the next three calendar months are automatically highlighted.

How to Highlight Renewal Dates 3 Months in Advance in Excel

Product: Excel
Device & OS: not provided
Scenario: Tracking upcoming contract, subscription, or employee certification renewal dates to ensure timely action.
Observed behavior: Dates due within exactly three calendar months from the current date need to be visually distinguished from other dates in the spreadsheet automatically.

Before you start

Ensure your renewal dates are formatted as valid date values in Excel, not as text strings, so the formula can correctly evaluate them against today's date.

Solution 1Recommended

Use Conditional Formatting with EDATE and TODAY Functions

Applying a formula-based conditional formatting rule using EDATE accurately calculates exactly three calendar months, preventing errors caused by varying month lengths.

While adding 90 days to the current date (e.g., TODAY()+90) is a quick approximation, using the EDATE function is highly recommended for accurate calendar month calculations. EDATE seamlessly accounts for months with 28, 29, 30, or 31 days.

Select the target column

Click and drag to select the cells in your Renewal Date column that you want to format (for example, B2:B100).

Open Conditional Formatting

Navigate 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 option

In the New Formatting Rule dialog box, select 'Use a formula to determine which cells to format'.

Enter the formatting formula

In the formula box, type =AND(B2>=TODAY(), B2<=EDATE(TODAY(),3)) replacing B2 with the first cell of your selected range. If you are using Excel Tables, you can use structured references like =AND([@[Renewal Date]]>=TODAY(),[@[Renewal Date]]<=EDATE(TODAY(),3)).

Apply formatting style

Click the 'Format' button, go to the 'Fill' tab, choose your preferred highlight color (like yellow or green), and click 'OK' twice to apply the rule.

Use Conditional Formatting with EDATE and TODAY Functions

Dynamic Automatic Updates: This formula automatically updates every time you open the spreadsheet based on your system's current date, ensuring your upcoming deadlines are always highlighted accurately without manual adjustments.

WPS Spreadsheet Solution

Highlight Upcoming Dates Easily with WPS Spreadsheet

WPS Spreadsheet offers powerful conditional formatting tools fully compatible with Microsoft Excel formulas, making it incredibly easy to track upcoming deadlines, renewals, and expiration dates.

1. Open your file: Launch WPS Spreadsheet and open your document containing the renewal dates.
2. Select your dates: Highlight the specific column or cells where your dates are located.
3. Access Conditional Formatting: Under the Home tab, click 'Conditional Formatting', then select 'New Rule' and 'Use a formula to format cells'.
4. Apply the rule: Enter the =AND(B2>=TODAY(), B2<=EDATE(TODAY(),3)) formula, choose a fill color, and click OK.

Fully compatible with Microsoft Excel formulas and xlsx formatsLightweight application that runs smoothly on all devicesFree built-in logical date functions like EDATE and TODAYFamiliar tabbed user interface for seamless migration

microsoft office alternative - wps office

Frequently Asked Questions

Why should I use EDATE instead of TODAY()+90?

The EDATE function accurately calculates calendar months, automatically adjusting for varying month lengths (28, 29, 30, or 31 days). Using TODAY()+90 simply adds 90 days, which can incorrectly push dates into the wrong month depending on the time of year.

Can I also highlight dates that are already past due?

Yes. You can add a second conditional formatting rule for the same column using the formula =AND(B2<>"", B2<TODAY()) and assign it a different color, such as red, to easily spot expired renewal dates.

Why isn't my conditional formatting rule working?

Ensure your dates are formatted as actual Date values rather than text strings. You can verify this by selecting the cell and checking that the format drop-down in the Home tab says 'Date'. Also, double-check that the cell reference in your formula exactly matches the top-left cell of your highlighted range.

Excel & Data Problems