Google-sheets – Conditional Formatting Based on Date Formula

conditional formattinggoogle sheets

I'm trying to highlight the contents of a Date cell if the following conditions are met:

Value of another cell = "No"; and
Value of target cell + 7 days > Today

The formula that I have right now is: =If(And((E2+7)>Today(),H2="No"))

Best Answer

You're on the right track, just get rid of If: the custom formula should be

=And((E2+7)>Today(), H2="No")

Explanation

If requires at least two arguments: condition and the result to be returned. Here you just have a condition, so if is neither suitable nor needed. The formula used for conditional formatting should return a boolean value (True/False), or a numeric value, which will be coerced to boolean as follows: 0 will be interpreted as False, nonzero as True. The formatting is applied if the formula returns True.

In your case, and returns a boolean value, which is all you need.

Related Solutions

Google Sheets – Formatting Cells Based on Upcoming Date

Suppose the dates are in column A, beginning with row 2, and cells to be formatted are in column B. Then you'd use conditional formatting with custom formula

=AND(A2<TODAY()+21, B2="no")

applying it to the range B2:B.

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