Excel Conditional Formatting for Contact Dates Older Than 18 Months
Question details
Create a conditional formatting rule that highlights a contact date when at least 18 months have passed.

- 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.
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.
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.
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.
On the Home tab, select Conditional Formatting > New Rule.
Select Use a formula to determine which cells to format.
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.
Select Format, choose a fill color, font color, or other style, then select OK. Select OK again to save the conditional formatting rule.

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. Select the contact data range: In WPS Spreadsheets, select the date cells or complete rows that should be highlighted, such as A2:F100.
- 2. Open the conditional formatting tool: Go to Home > Conditional Formatting > New Rule.
- 3. Choose a formula rule: Choose the option to use a formula for determining which cells to format.
- 4. Add the date formula: Enter =EDATE($A2,18)<=TODAY(), using the first row number of your selected data range.
- 5. Choose and apply formatting: Select a fill or font style for overdue contacts, then click OK to apply the rule.

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.




