CODEWITHSUNDEEP
excelconditional-formatting
T.Hill
T.Hill

Reputation: 11

Format a cell based on conditions in different cells

I am trying to highlight a cell red if the date is more than 28 days old and the next cell is blank. I tried to do conditional formatting by using the following rule:

=and(a1 < today()-28, b1="not blank")

It didn't give me an error, but it also didn't highlight the appropriate cells.

Why is this not working?

Upvotes: 0

Views: 1229

Answers (2)

TotsieMae
TotsieMae

Reputation: 835

If I'm understanding your question correctly, you want your conditional formatting to apply to a given cell when A1 is more than 28 days older than today's date AND when B1 does not contain a date.

If so, try using this for your conditional format formula:

=AND($A1<TODAY()-28,NOT(ISNUMBER($B1)),$B1>0)


EXPLANATION:
With the AND function wrapping all of this up, all three conditions

$A1<TODAY()-28
&
NOT(ISNUMBER($B1))
&
$B1>0

will need to evaluate to TRUE to make the conditional formatting work.

The NOT(ISNUMBER($B1)) works by first determining if $B1 is a number. ISNUMBER($B1) returns TRUE if $B1 is a number and FALSE if $B1 isn't a number (remember, dates are numbers in Excel). Then theNOT function will return the opposite of what ISNUMBER just determined (either FALSE or TRUE).

By then checking if $B1 is greater than 0, you're ensuring that $B1 contains a number that is positive and could therefore be an actual date.

Upvotes: 1

Balinti
Balinti

Reputation: 1534

Just try to use the isblank function =and(a1< today()-28,isblank(b1)) you can use it with the opposite by using *-1 if needed

Upvotes: 0

Related Questions