Conditionally Formatting if another column contains a specific date

Report
-
Posts
2747
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
May 27, 2021
-
Hello,

I have a spreadsheet where I am tracking requests. I want Excel to highlight the row if the item due date (column B) is NEXT month or has PASSED (for when we receive late requests), but I only want it to highlight if the 'Date Completed' (column I) is not filled in. Once it is filled in, I want the highlight to go away.

I have tried multiple variations using both the AND, IF & ISBLANK function and cannot figure it out. Please help!

1 reply

Posts
2747
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
May 27, 2021
462
Hi cjf15,

The due date being next month or being passed will fit in the same condition:
MONTH($B2)<=MONTH(TODAY())+1

To NOT highlight the row when the corresponding cell in column B is empty (so you can apply the CF to rows which don't contain a due date yet):
$B2<>""

To ONLY highlight the row when the corresponding cell in column I is empty:
$I2=""

We then combine the above in an AND formula:
=AND(MONTH($B2)<=MONTH(TODAY())+1,$B2<>"",$I2="")

Best regards,
Trowa

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!