Question about syntax for conditional formatting

[Cbeckwith]

See below:

Can someone explain the following in simple language;

=AND($B$12<>"",$B$12<TODAY()+120)

What does the <> after the cell location mean? and why the double quotes ""?
I thought the double quotes meant whatever is in the cell is NULL....I have a
date in the $B$12 position.

 

[Mike H]

Hi

The formula will evaluate as TRUE or FALSE and is checking 2 conditions
which must both be TRUE

=AND($B$12<>"",$B$12<TODAY()+120)

the AND is the bit that ensure both must be true to return TRUE

Condition 1 checks that B12 has data in the cell i.e <> means 'does not equal'
$B$12<>""


Condition 2 checks that the date in the cell is less than (<) 120 days in
the future
$B$12<TODAY()+120

The reason condition 1 is required is that B12 would evaluate as zero if it
was empty and cause the formula to evaluate as TRUE
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[Stefi]

The condition becomes true if cell $B$12 is not blank and the date in it is
earlier than today + 120 days.

--
Regards!
Stefi



„Cbeckwith†ezt írta:

 

[Cbeckwith]

Mike, Thanks for this information, very helpful. Now my question becomes if
the B12 date is a future date, not a past date, how can I write the formula
to look at the date (B12) in comparision to Today's actual date + 120 days
into the future? Do I just change the < to a > today()+120 ?

 

[Cbeckwith]

This formula is just giving me "Pink" color on background no matter what date
I give it in the b12

=AND($B$12<>"",$B$12>TODAY()+60)
Formats to pink background

=AND($B$12<>"",$B$12>TODAY()+120)
Formats to a yellow background

=AND($B$12<>"",$B$12>TODAY()+180)
Formats to a green background

According to what I see, it should give me the different colors depending.

 

[Cbeckwith]

Thank You for all your comments, Very useful.

 

[Stefi]

Change the order of the three conditions like this:

=AND($B$12<>"",$B$12>TODAY()+180)
Formats to a green background

=AND($B$12<>"",$B$12>TODAY()+120)
Formats to a yellow background

=AND($B$12<>"",$B$12>TODAY()+60)
Formats to pink background

If the first condition evaluates to True, the other two will NOT be
evaluated!

--
Regards!
Stefi



„Cbeckwith†ezt írta: