Conditional Formatting based on next month date and blank cell

%3CLINGO-SUB%20id%3D%22lingo-sub-2387169%22%20slang%3D%22en-US%22%3EConditional%20Formatting%20based%20on%20next%20month%20date%20and%20blank%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2387169%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20everyone!!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20spreadsheet%20where%20I%20am%20tracking%20requests.%20I%20want%20Excel%20to%20highlight%20the%20row%20if%20the%20item%20due%20date%20(column%20B)%20is%20NEXT%20month%20or%20has%20PASSED%20(for%20when%20we%20receive%20late%20requests)%2C%20but%20I%20only%20want%20it%20to%20highlight%20if%20the%20'Date%20Completed'%20(column%20I)%20is%20not%20filled%20in.%20Once%20it%20is%20filled%20in%2C%20I%20want%20the%20highlight%20to%20go%20away.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20tried%20multiple%20variations%20using%20both%20the%20AND%2C%20IF%20%26amp%3B%20ISBLANK%20function%20and%20cannot%20figure%20it%20out.%20Please%20help!%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2387169%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2388652%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20based%20on%20next%20month%20date%20and%20blank%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2388652%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%20for%20your%20suggestion!%20That%20did%20not%20work%20though.%20It%20highlighted%20all%20of%20the%20items%20in%20column%20B%20that%20had%20passed%2C%20regardless%20if%20column%20I%20was%20filled%20in%20or%20not.%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F570951%22%20target%3D%22_blank%22%3E%40mtarler%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New 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)