Forum Discussion
Conditional Formatting multiple conditions
Hey all! I need some help writing a formula for some conditional formatting I'm looking to do for a contact log.
Basically, I want to highlight dated cells if the interaction with a contact is -14 days and -21 days from today's date. I've figured that out and am using the formula
=DATEDIF($E7,$F$1,"D")>14
and
=DATEDIF($E7,$F$1,"D")>21
However I also want to run conditional formatting so that the cells only highlight when the Contact info in the log is a unique value, and this is what I'm currently struggling with. For Confidentiality, I can't show the values populated in the Contact Column, but I can show an example of my table's columns if that helps.
So in essence, I want the date formatting to run only if the contact value is a unique value, so that it is not flagging an old log of an interaction and only flagging the most recent interaction with that contact name if it is dated past 2-3 weeks. Is there anything I can build that will operate in this way?
Thanks!
This is possible, and you’re thinking about it exactly the right way.
The key idea is:Only apply the date-based conditional formatting to the most recent entry per Contact
That means we must:
- Identify whether the row is the latest date for that contact
- Only then apply your 14 / 21 day rules
Core concept (important)
Conditional Formatting formulas run per row.
So the formula must answer TRUE only for the latest row of each contact.We do that by comparing:
- The date in the current row
- Against the MAX date for that contact
Assumptions (adjust if needed)
Based on your screenshot and description:
Column
Meaning
E
Interaction Date
F1
Today’s date (or TODAY())
G
Contact name
Rows
Start at row 7
Step 1: Formula to detect “latest contact interaction”
This formula returns TRUE only if this row has the most recent date for that contact:
=E7 = MAXIFS($E:$E, $G:$G, $G7)
This filters dates by contact
Works in Excel 365
No helper column neededStep 2: Combine with your 14-day rule
Yellow (older than 14 days, but not 21)
=AND(E7 = MAXIFS($E:$E, $G:$G, $G7),
DATEDIF(E7, $F$1, "D") > 14,
DATEDIF(E7, $F$1, "D") <= 21)
Step 3: Red (older than 21 days)
=AND(E7 = MAXIFS($E:$E, $G:$G, $G7),
DATEDIF(E7, $F$1, "D") > 21)
MAXIFS() finds the newest interaction per contact
Conditional Formatting ignores rows that aren’t the newest
No volatile formulas, no VBA, no helper columns
Works cleanly inside a table
My answers are voluntary and without guarantee!
Hope this will help you.
Was the answer useful? Mark as best response and like it!
This will help all forum participants.
2 Replies
Thank you so much, this is exactly what I was after! So glad to know that I don't have to set up a VBA to run this, much appreciated!!
This is possible, and you’re thinking about it exactly the right way.
The key idea is:Only apply the date-based conditional formatting to the most recent entry per Contact
That means we must:
- Identify whether the row is the latest date for that contact
- Only then apply your 14 / 21 day rules
Core concept (important)
Conditional Formatting formulas run per row.
So the formula must answer TRUE only for the latest row of each contact.We do that by comparing:
- The date in the current row
- Against the MAX date for that contact
Assumptions (adjust if needed)
Based on your screenshot and description:
Column
Meaning
E
Interaction Date
F1
Today’s date (or TODAY())
G
Contact name
Rows
Start at row 7
Step 1: Formula to detect “latest contact interaction”
This formula returns TRUE only if this row has the most recent date for that contact:
=E7 = MAXIFS($E:$E, $G:$G, $G7)
This filters dates by contact
Works in Excel 365
No helper column neededStep 2: Combine with your 14-day rule
Yellow (older than 14 days, but not 21)
=AND(E7 = MAXIFS($E:$E, $G:$G, $G7),
DATEDIF(E7, $F$1, "D") > 14,
DATEDIF(E7, $F$1, "D") <= 21)
Step 3: Red (older than 21 days)
=AND(E7 = MAXIFS($E:$E, $G:$G, $G7),
DATEDIF(E7, $F$1, "D") > 21)
MAXIFS() finds the newest interaction per contact
Conditional Formatting ignores rows that aren’t the newest
No volatile formulas, no VBA, no helper columns
Works cleanly inside a table
My answers are voluntary and without guarantee!
Hope this will help you.
Was the answer useful? Mark as best response and like it!
This will help all forum participants.
