Google Sheets – Flag Empty Cells if Date in Another Cell Has Passed

conditional formattingformulasgoogle sheets

Good afternoon!

I've created a test sheet to help visualize this – https://docs.google.com/spreadsheets/d/1-qHH4GfeIZz-7OBHFpp4trrFNg7l-gBHDT2MHg9tFmE/edit?usp=sharing

I'm aiming to have cells highlight only if all the following conditions are met –

The cell in Column C is empty
The date in Column B is not empty
The date in Column B has passed

I've thrown together a few things that partially work but will either highlight cells in Column C even if Column B's date is blank or will highlight all cells in Column C that are past Column B's date – even if the cell in Column C is populated.

Ideally, in the example sheet, cells C2 and C3 would be highlighted while C5-C7 remain blank.

This seems incredibly simple but I'm at the end of my rope with it… Any pointers?

Best Answer

=COUNTIFS(C2,"=",B2,"<>",B2,"<"&TODAY())

Related Solutions

Google-sheets – How to check if a cell is blank for Conditional formatting

With the range set to: B3:O1000

Try:

=AND(ISBLANK(B3),INT($A3)=INT(today()))

or even shorter:

=AND(ISBLANK(B3),$A3=today())

Google Sheets – How to Use IF, AND Conditional Formatting

in this conditional-formatting-scenario is =ArrayFormula() kind of not necessary.
to hide #N/A you can use =IFERROR()
to avoid #N/A you can use =ISNUMBER =ISDATE =ISBLANK =ISTEXT etc.
and this custom formula is what you seek:

=IF( AND(ISNUMBER(J1:J);
         NOT(ISBLANK(L1:L))); COUNTIFS(J1:J; ">0"; 
                                       K1:K; ">"&DATE(0;1;1)))

it checks if J has number
and L is not blank
and then it checks if J>0
and K >1st january of year 0.

be sure you have right formatting otherwise it wont work:

update 1:

https://docs.google.com/spreadsheets/d/

update 2:

https://docs.google.com/spreadsheets/d/ where custom formula is:

=IF( AND(ISNUMBER(K2), 
         ISBLANK(M2), 
         ISDATE(L2)), COUNTIFS(K2, ">0", 
                               L2, ">"&DATE(0,1,1)))