How to Highlight Dates Older Than One or Two Years in Excel

This setup is for Excel and applies to column H starting at H3. Select a larger range such as H3:H1000 if you want the formatting to continue well beyond the current data.

Conditional formatting to highlight dates by age

Use a green fill when the date is within the last year from today.

Use an orange fill when the date is older than one year.

Use a red fill when the date is more than two years old.

Use a blue fill when the cell contains the exact text Never.

What the rules rely on

• TODAY() returns the current date.
• EDATE(TODAY(),-12) moves back 12 months.
• EDATE(TODAY(),-24) moves back 24 months.
• The text rule checks for the exact value "Never".

Apply the rules to the date range

In Excel, start by selecting H3:H1000, then create each rule from Home > Conditional Formatting > New Rule. Using a longer range keeps the formatting active for future entries.

Create the red rule for nonblank cells

Choose Format only cells that contain. Keep the first dropdown as Cell Value, set the second to not equal to, and enter ="".

Click Format, open the Fill tab, choose red, then click OK twice.

Add the orange rule for dates at least two years old

Create another rule with greater than or equal to and enter =EDATE(TODAY(),-24).

Set the fill color to orange. This marks dates that are 24 months old or older.

Add the green rule for dates within the last year

Create one more rule using greater than or equal to and enter =EDATE(TODAY(),-12).

Set the fill color to green. This catches dates from the last 12 months through today.

Add the blue rule for the text Never

Create a final rule using equal to and enter ="Never".

Set the fill color to blue. The cell will turn blue whenever that exact text is entered instead of a date.

H3:H1000 is a practical starting range if you want the rule to keep working below row 84.

Home > Conditional Formatting > New Rule is the exact entry point for each rule.

=EDATE(TODAY(),-12) and =EDATE(TODAY(),-24) compare each date to today.

="Never" must match the text exactly for the blue fill to appear.

Check the result

After all four rules are saved, column H should respond automatically based on the age of each date or the text value entered.

Important limitation to keep in mind

• The source provides the exact formulas and fill colors, but it does not define rule priority or stop-if-true behavior.
• If your workbook behaves differently, review the order of rules in Conditional Formatting Rules Manager.
• The blue rule works only when the cell contains the exact word Never, not a variation such as never or Never entered with extra spaces.

A simple four-rule setup keeps date aging visible at a glance

For this Excel task, the working method is straightforward: apply conditional formatting to column H, use EDATE with TODAY for the one-year and two-year thresholds, and add a separate text rule for Never. Extending the selected range up front makes the format more useful over time.

Get the same date highlighting done faster

Use the same formulas, color logic, and verification workflow in your spreadsheet, then keep the range ready for future rows in column H.

Complete This Workflow with WPS Office

Use Word, Excel, and PPT for FREE

Free Download

WPS Office can apply this date-age highlighting directly in WPS Spreadsheets with formula-based conditional formatting.

1. Open a duplicate workbook in WPS Spreadsheets and select the date range, starting from the first data row such as H2:H200.
2. Open Home > Conditional Formatting and add a blue formula rule for text values: =$H2="Never".
3. Add formula rules for dates: red for =AND(ISNUMBER($H2),$H2, orange for dates between 12 and 24 months old, and green for dates within 12 months.
4. Enter four test values—Never, a recent date, a date 18 months ago, and a date 30 months ago—and confirm rule order and colors before applying the range broadly.

Use mutually exclusive formulas so an old date is not colored by more than one age rule.

Highlight Dates Older Than One or Two Years in Excel FAQs

How far down should I apply the conditional formatting range?

Use a larger range such as H3:H1000 if you expect more entries later. The source specifically recommends extending the range beyond the current rows so the formatting continues down the same column.

What formula marks dates that are more than two years old?

Use a rule with greater than or equal to and enter =EDATE(TODAY(),-24) . Then assign the orange fill color defined in the source workflow.

Why is the blue format not appearing when I type Never?

The rule must use equal to with ="Never" , and the cell text must match exactly. If the text has different capitalization or extra spaces, the blue fill may not trigger.

Why do these date colors update automatically over time?

The orange and green rules use EDATE(TODAY(),-24) and EDATE(TODAY(),-12). Because TODAY() changes each day, the thresholds move automatically without editing the rule again.