Conditional Formatting Based on Date Formula

Question

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

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

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

Answer 1

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.