Conditional Formatting for dates

Daveyboy1981  conditional formatting custom formulas can be a little tricky.  Let me see if I can help.

1) in the formula box you type = [condition] and if that [condition] evaluates to be true the formatting will be applied. if false it will not.

2) this is where it can get confusing.  that [condition] formula can use absolute cell references (e.g. $a$1) or relative cell references (e.g. a1) or mixed (e.g. a$1).  Any relative reference will be relative to the upper left corner of the range(s) that the conditional formatting is being applied.

so what you want is something like the following:

If the area being checked is B2:Z100 then

= B2<=TODAY()                            [set formatting to green]

meaning: if the date found in the cell is < (before) today then highlight green

and another rule for the same range (B2:Z100) for the yellow:

= B2 >= TODAY()-1035    (which is about 2 yrs 10months) [set to yellow]

or if you want to be more precise:

=B2 >= DATE( YEAR(NOW())-2, MONTH(NOW())-10, DAY(NOW()))

meaning: if the date found in the cell is > (after) 2year and 10months before today then highlight yellow

IF you used B3 for example then when it is checking B2 (the upper left corner) it would be LOOKING at the value in B3 and if B3 met the criteria then it would highlight B2.  And this looking at the cell 1 row below it would happen over the entire range of B2:Z100 so cell Z100 would be highlighted IF Z101 met the criteria.

I hope that helps you understand how it works so you can make your formulas work.