Formula Errors

How to Highlight the Latest Contact Interaction Using Conditional Formatting in Excel

Jason MillerJason Miller Nov 10, 2026 870 views

Question details

The user wants to apply conditional formatting to visually highlight the most recent interaction date for a specific contact, specifically when that interaction occurred more than 14 or 21 days ago.

How to Highlight the Latest Contact Interaction Using Conditional Formatting in Excel
Product
Spreadsheet
Device & OS
not provided
Scenario
Tracking customer or client interactions in a spreadsheet and dynamically flagging the newest records that exceed a specific age threshold to determine who needs follow-up.
Observed behavior
The user needs a formula-driven conditional formatting rule to calculate the maximum date per contact and evaluate its age against today's date, successfully avoiding the use of VBA.
Before you start

Ensure your interaction data is structured in columns (e.g., dates in column E, contacts in column G), and designate a specific cell containing today's date for accurate age calculations.

Solution 1Recommended

Apply MAXIFS and DATEDIF as a Conditional Formatting Rule

Use the MAXIFS function to identify the most recent date for each specific contact, and nest it with the DATEDIF function to calculate how many days have passed since that interaction.

By combining logical functions, you can force the spreadsheet to evaluate both the newest record and its age simultaneously. This method is highly dynamic and automatically updates when new rows of interaction data are added, completely replacing the need for VBA macros.

1
Define your data layout

Assume your interaction dates are recorded in column E, contact names are in column G, your data begins on row 7, and today's date is entered in cell F1.

2
Select the target data range

Highlight the entire range of cells or rows starting from row 7 that you want the formatting to apply to.

3
Create a new formatting rule

Navigate to the Home tab, click on 'Conditional Formatting', select 'New Rule', and choose 'Use a formula to determine which cells to format'.

4
Enter the formula logic

In the formula bar, enter: =AND(E7=MAXIFS($E:$E,$G:$G,$G7),DATEDIF(E7,$F$1,"D")>14,DATEDIF(E7,$F$1,"D")<=21) to highlight newest interactions older than 14 days but less than or equal to 21 days.

5
Set the format and apply

Click the 'Format' button, select a highly visible fill color (like yellow or light red), click OK, and then OK again to apply the rule to your selection.

Apply MAXIFS and DATEDIF as a Conditional Formatting Rule
No VBA Required: This formula successfully manages complex condition-checking natively within the conditional formatting engine, saving you from writing or maintaining custom VBA scripts.
Solve it with WPS Office

Track Data Easily in WPS Spreadsheet

WPS Spreadsheet fully supports advanced logical formulas like MAXIFS, DATEDIF, and complex Conditional Formatting rules, allowing you to build dynamic tracking logs without hassle.

  1. 1. Open your interaction log: Launch WPS Spreadsheet and open the document containing your contact history.
  2. 2. Access Conditional Formatting: Highlight your data range, navigate to the 'Home' tab, and click 'Conditional Formatting' > 'New Rule'.
  3. 3. Apply your formula: Select 'Use a formula...', input your MAXIFS and DATEDIF logic, choose a highlight color, and click 'OK'.
100% compatible with Microsoft Excel conditional formatting rulesNative support for complex nested functions including MAXIFS and DATEDIFUser-friendly interface for managing multiple formatting rulesLightweight, fast, and free to use for your daily data tracking
microsoft office alternative - wps office

Frequently Asked Questions

Why does my conditional formatting formula return an error or highlight the wrong rows?

This usually happens due to misaligned cell references. Ensure that the row number in your relative references (like E7 and G7) exactly matches the top row of your selected data range. Also, verify that absolute references ($E:$E) are used for the lookup columns.

Can I use the TODAY() function directly instead of referencing a cell with today's date?

Yes. Instead of referring to cell $F$1, you can embed the TODAY() function directly into the DATEDIF calculation. The updated snippet would look like: DATEDIF(E7,TODAY(),"D").

What if I am using an older version of Excel or Spreadsheet that doesn't support MAXIFS?

If MAXIFS is unavailable in your software version, you can achieve the same result by substituting it with an array formula utilizing MAX and IF. You would replace the MAXIFS section with: MAX(IF($G:$G=$G7,$E:$E)). The conditional formatting engine handles this array logic automatically.