Conditional formatting w/color

[airmanrobert]

I maintain a large list of people and track training dates. My problem is that I need a way to conditionally format cells so that when these dates turn a year old, they highlight.
ex. Supervisor Safety - Completed 12Jan01
I need this date highlighted since it is more than a year old......

PLEASE HELP!!!! This is driving me nutz!

[Tom Urtis]

Let's say your list starts in cell A2; follow these steps:

(1) Select A2.
(2) Click on Format > Conditional Formatting.
(3) Under the script Condition 1, click the down arrow and select "Formula Is".
(4) In the field to the right, enter (or paste in) this formula:
=A2-DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY()))<=0
(5) Click the Format... button, and choose your formatting preferences.
(6) Click OK, then OK.
(7) Copy A2 and paste special for Formats as far down as you need to.

Tom Urtis

=Now()-A2>365.25 should also work.

[Tom Urtis]

That it will, plus it's *just a few* characters shorter than mine.

Thanks.

T.U.

[Brian from Maui]

Anon or Tom

I've tried using this with 11 months but no luck. I reduced 365.25 to 335.25 and get a "false" response.

Brian

[Brian from Maui]

Guys,

I got it. Using the wrong <>!!!

Brian

[airmanrobert]

You guys are great!!! If you ever come to Okinawa, the beer is on me!

Got one last question... what is the .25 on the end of 365 for?

Thanks
Rob

[Juan Pablo González]

A year doesn't have exactly 365 days, that's why every 4 years we have a Feb 29th, it's just a way of adding the 1/4 of a day that each year has.