Question about syntax for conditional formatting

  • Thread starter Thread starter Cbeckwith
  • Start date Start date
C

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.
 
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.
 
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:
 
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 ?
 
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.
 
Change the order of the three conditions like this:
=AND($B$12<>"",$B$12>TODAY()+180)
Formats to a green background
Click to expand...
=AND($B$12<>"",$B$12>TODAY()+120)
Formats to a yellow background
Click to expand...
=AND($B$12<>"",$B$12>TODAY()+60)
Formats to pink background
Click to expand...

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

--
Regards!
Stefi



„Cbeckwith†ezt írta:
 
Back
Top