Conditionally Formatting if another column contains a specific date

Solved/Closed
cjf15 - May 26, 2021 at 03:45 PM
TrowaD Posts 2913 Registration date Sunday September 12, 2010 Status Moderator Last seen November 21, 2022 - May 27, 2021 at 12:07 PM
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

TrowaD Posts 2913 Registration date Sunday September 12, 2010 Status Moderator Last seen November 21, 2022 541
Updated on May 27, 2021 at 12:10 PM
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

0