Thank you Bernie!

Now, how do apply that to the spreadsheet? The range of cells D2 through
J18 all have date cells. Sorry to be so dense.
--
Dean Johnson


"Bernie Deitrick" wrote:

Dean,

It didn't work because DATE requires year, month, and day to be passed to it.

Try using one of these two formulas - not sure what specifically you mean by "within one year"

To highlight dates that occured exactly one year ago today

=TODAY() = (A1+365)

To highlight dates within a range (you are within +/- 5 days of the anniversary)

=AND(TODAY() = (A1+360), TODAY() <= (A1+370))

HTH,
Bernie
MS Excel MVP


"Dean Johnson" wrote in message
...
I too need the red function based on date. I have cells with dates (G2, G10,
etc with dates like 22-Jul-07) and need that cell to go red when the date
hits 365 days from the day displayed. I tried using "ker_01"s advise but
don't get any result at all with the "if date...". If I change if to "=" I
get result "FALSE"

Perhaps "ker_01" could provide more exact info? Please?
--
Dean Johnson


"ker_01" wrote:

Use the "formula" option;

if date() (A1+365) 'then select red
if date() (A1-31) 'then select amber
if date() <= (A1-30) 'then select green

Alternatively, skip condition three and just format the cell colors to
green. If either condition 1 or 2 are met, they should supersede the
background color.

If 31 days is not precise enough (if you need exactly one month, regardless
of leap years, etc) then reply to the group for assistance looking at the
month() and year() formulas to get an exact month prior.

HTH
Keith

"Carl Mountney" wrote:

Hi

I have been scratching my head to come up with a way of doing the following.

Imagine there is a date entered in cell J3
I would like the cell to be shaded green up untill one month before the
anniversary of the date when I would like it to change to amber. Once the
date in cell J£ is over a year old I would like it to change to red.

Any ideas??
Thanks
Carl