How to solve conditional formatting ruling 'TRUE' when cell is blank

Hi there,

This is my first post here and I believe I will be coming back quite a lot as I am currently building an excel worksheet/program to prioritize my workload. I currently have a rule set for one of my cells (A). Cell (A) will turn red IF it is blank AND IF the shipping date (B) in another cell is less than or equal to today's date using the following formula:

=IF(ISBLANK(A), AND((B)<=TODAY()))

The problem is, if there is no date entered in (B), then the formula is TRUE for some reason, causing (A) to be highlighted red and I'm not sure why.

I tried the following equation to fix it (though I am no expert):

=IF(ISBLANK(A), AND(IF(NOT(ISBLANK(B)))), AND((B)<=TODAY()))

The formula did not work and I can't find the proper way to write it. I then tried to create another rule to override it using, IF(ISBLANK(B)), but that did nothing as well.

Any help will be greatly appreciated! More questions to come.

If a cell is blank, and then used in an arithmetic expression, it will be treated as 0. So if B is blank, it will always be < TODAY().

You cannot use A and B in this formula. You must use cell references, like A1 and B1.

I also think your boolean logic is a bit challenged. You are using an IF function, but only returning a value when the condition is TRUE. FALSE will be returned by default in the FALSE case, which is probably OK for conditional formatting. In any case, for conditional formatting it is usually better to use a boolean expression that just evaluates to TRUE or FALSE without wrapping an IF around it.

I don't know if A and B have formulas that return blanks, or are simply empty. If a cell has a formula that returns a blank, like

=IF(Z9>4,"","")

then ISBLANK will be FALSE, even though the cell looks blank.

Please try this formula:

Formula:

Please Login or Register  to view this content.

Hi and welcome to the forum

As a CF

Formula:

Please Login or Register  to view this content.

No, it will not cancel.
As it is used inside AND, both conditions B1>0 and B1<=TODAY() has to be fulfilled at the same time.

So let's see at Jan 1st, 2020 - it was 43831 day in Excel calendar. It is both >0 and <= TODAY(), as today = Feb 18, 2020 is 43879th day of Excels calendar



The result of whole formula will be true only if ALL 3 tests will be true

..and just to conclude Kaper's explanation one of the conditions was that B1 should not be blank. If B1 is blank it will evaluate as zero and B1>0 will be False and hence the whole AND() function will be false. So we test for B1 being greater than zero