Google-sheets – Google Sheets Conditional Formatting For Dates and a Blank Row

google sheets

In Google Sheets I am trying to use conditional formatting to change the format of a column of cells if the date in that column it more that 14 days before today, AND if a separate column is blank.

This is what I have so far, but it doesn't work.

Fore the specific range I want: C2:C287

Custom Formula:

=TODAY()-14 =AND =ISBLANK(L2:L287)

Any help would be greatly appreciated.

Best Answer

With C2:C287 selected, try this as the custom CF formula:

=AND(C2<TODAY()-14,ISBLANK(L2))

Conditional formatting rules only need the first cell in the range (and any relative ranges) in order to extrapolate the relationship to every other cell in the range.

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 – Google sheets function 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)))