Hi,
I have written formulas to add fill to cells based on distance out from a future date (i.e. at >90 days, 90-61, 60-31, and 30-0 days out from future date fill green, yellow, orange, or red). (See image)

This works fine, but I'd like to add to the formula so that if a cell on the same row in a different column has any date entered into it, no conditional formatting is applied. Said another way, if my conditional formatting is in E2, and any date is entered in G2, it would override the conditional formatting and no color is applied.

Also, Excel defaulted to including an AND statement in the 2nd, 3rd, and 4th condition. I guess I'm just not clear why that's appropriate since the conditions are all independent of each other and cannot exist at the same time, though I understand why the OR statement is also not appropriate.



Thanks,
Henry

Attached Images

ConditionalFormatting.png (19.9 KB, 14 views)

Hi
to start with you should replace E2 in your CF with $E2
Your formula for green should be =($E2-today())>90
The others can just boil down to
=($E2-today())>=61
=($E2-today())>=31
=($E2-today())>=0
As for including the condition in col G you did not specify what it MIGHT contain except a date
Supposing the cells in G contain a date or are empty, try adding a condition to all of your previously defined CF's, like
=AND($G2="",($E2-today())>90)
etc..

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

It's difficult for Excel to determine if a date is in a cell because Excel stores dates as numbers but the format of the cell is a date format of some kind. What's more, if someone enters a date like 24th Dec 23, Excel won't necessarily recognise it as a date even though it's obviously a date to us humans. It is possible but it's convoluted and still not a certainty that it'll get it right.
So to avoid all that, would it do if either the cell wasn't blank, or perhaps was a number in order to decide whether the conditional formatting showed or not? It would just be a matter of adding a 3rd argument to the AND part of the conditional formatting formula.