Anniversary of a date is due - conditional formatting

[Carl Mountney]

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

 

[ker_01]

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

 

[ker_01]

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

 

[Dean Johnson]

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?

 

[Bernie Deitrick]

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]

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.

 

[Bernie Deitrick]

Dean,

Select cells D2:J18, with cell D2 being the activecell.

Then use (XL2003 or earlier) Format / Conditional Formatting... in the dropdown choose "Formula
is"

In 2007, use the CF button, then choose "Highlight Cell Rules" "More Rules" "Use a formula".....

In either case, use the formula with D2 in the place of A1:

=TODAY() = (D2+365)
or
=AND(TODAY() >= (D2+360), TODAY() <= (D2+370))

Just like regular formulas, Excel will update the CF formula for each cell, so the D2s become
D3s......J18s

HTH,
Bernie
MS Excel MVP

 

[Dean Johnson]

Thanks again Bernie - it's great that you're so fast.

Did as described, but nothing changed. It's Office 2007 if that helps.

Any other ideas?

Dean

 

[Bernie Deitrick]

Dean,

If thevalues are not true dates, but are strings that look like dates, then
try

=TODAY() = (DATEVALUE(D2)+365)

etc...

PS. you also need to set the fill color in the CF dialog.... some folks miss
that part.

HTH,
Bernie
MS Excel MVP