Conditionally Formatting if another column contains a specific date

[Solved]
Report
-
Posts
2811
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 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
2811
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 27, 2021
484
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