Formatting Issues

Excel Conditional Formatting for Contact Dates Older Than 18 Months

Chanuka GeekiyanageChanuka Geekiyanage Nov 12, 2026 868 views

Question details

Create a conditional formatting rule that highlights a contact date when at least 18 months have passed.

Excel Conditional Formatting for Dates Older Than 18 Months
Product
Microsoft Excel
Device & OS
not provided
Scenario
The user wants to identify contacts that have not been contacted for 18 months or longer based on dates stored in column A.
Observed behavior
Cells in the selected range should receive formatting when the corresponding contact date is 18 months old or older.
Before you start

Make sure the contact dates in column A are real Excel dates rather than text. Select the cells or rows you want to highlight before creating the rule.

Solution 1Recommended

Create an Excel rule using EDATE and TODAY

Use this rule when the contact date is in column A and you want to format a selected cell range or entire rows after 18 months have elapsed.

The EDATE function adds 18 months to the contact date, and TODAY compares that result with the current date. The rule becomes true when the contact date is at least 18 months old.

1
Select the range to format

Select the cells you want Excel to highlight. For example, select A2:A100 to format only dates, or select A2:F100 to format full contact rows.

2
Open Conditional Formatting

On the Home tab, select Conditional Formatting > New Rule.

3
Choose a formula-based rule

Select Use a formula to determine which cells to format.

4
Enter the 18-month formula

In the formula box, enter =EDATE($A2,18)<=TODAY(). Replace 2 with the first data row in your selected range; keep $A fixed if the contact date is always in column A.

5
Set the highlight style

Select Format, choose a fill color, font color, or other style, then select OK. Select OK again to save the conditional formatting rule.

Create an Excel rule using EDATE and TODAY
Adjust the row reference for your data: If your first selected data row is row 1, use =EDATE($A1,18)<=TODAY() instead. The row number in the formula must match the first row of the selected range.
Conditional Formatting in WPS Spreadsheets

Highlight Contact Dates Older Than 18 Months in WPS Spreadsheets

WPS Spreadsheets can apply the same EDATE and TODAY formula through Conditional Formatting to flag contacts that need follow-up. Select your data range, create a formula rule, and choose a visible format for overdue contact dates.

  1. 1. Select the contact data range: In WPS Spreadsheets, select the date cells or complete rows that should be highlighted, such as A2:F100.
  2. 2. Open the conditional formatting tool: Go to Home > Conditional Formatting > New Rule.
  3. 3. Choose a formula rule: Choose the option to use a formula for determining which cells to format.
  4. 4. Add the date formula: Enter =EDATE($A2,18)<=TODAY(), using the first row number of your selected data range.
  5. 5. Choose and apply formatting: Select a fill or font style for overdue contacts, then click OK to apply the rule.
Automatically flags contact dates that are 18 months old or olderSupports EDATE, TODAY, and formula-based conditional formatting rulesOpens and saves Microsoft Excel .xlsx and .xls filesProvides a familiar spreadsheet interface for Excel users
microsoft office alternative - wps office

Frequently Asked Questions

Why does the conditional formatting formula use $A2 instead of A2?

The $ locks the column to A so Excel always checks the contact date column as the rule is applied across a row. The row number remains relative, allowing Excel to evaluate each record separately.

How can I highlight the entire contact row instead of only the date cell?

Select the full table range, such as A2:F100, before creating the rule. Use =EDATE($A2,18)<=TODAY() so every cell in each qualifying row is formatted based on its column A date.

What should I do if Excel does not highlight an old contact date?

Check that the value in column A is stored as a valid date, not text. You can test it by changing the cell format to Short Date or using =ISNUMBER(A2), which should return TRUE for a real date.

Can I change the rule from 18 months to another period?

Yes. In Home > Conditional Formatting > Manage Rules, edit the formula and replace 18 in =EDATE($A2,18)<=TODAY() with the number of months you need.

Will the highlighted contacts update automatically each day?

Yes. Because the rule uses TODAY(), Excel recalculates the comparison using the current date whenever the workbook recalculates or is opened.