Conditional Formatting based on next month date and blank cell

Copper Contributor

Hello everyone!!

 

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! :) 

3 Replies
try this: =($B1<EOMONTH(TODAY(),1))*($I1="")

Thank you for your suggestion! That did not work though. It highlighted all of the items in column B that had passed, regardless if column I was filled in or not. @mtarler 

Sorry, that is a text-number thing, my bad. Maybe try one of these options:
=($B1<EOMONTH(TODAY(),1))*NOT(ISBLANK($I1))
=($B1<EOMONTH(TODAY(),1))*(LEN($I1)>1) (or whatever the min length of the items in Column I)