Hi,

I am currently trying to change the cells different colours when certain
dates are added and time frames are reached. So far i have got the cells
changing to green when a date is added and once the date is a year old it
turns to red using the below formula

=AND(ISNUMBER(H13),H13<=(TODAY()-365))

(where H13 is the cell the date is in)

Now i would like the cell to change orange 3months before the year is out
and red when the year is up, but i have been unable to do it thus far.

if anyone could help that would be much appreciated

thanks


vicky

Vicky

For the year

=(YEAR(A1)-YEAR(TODAY()))0

and month
=((A2)-TODAY())90

Mike


"Vicky" wrote:

Hi,

I am currently trying to change the cells different colours when certain
dates are added and time frames are reached. So far i have got the cells
changing to green when a date is added and once the date is a year old it
turns to red using the below formula

=AND(ISNUMBER(H13),H13<=(TODAY()-365))

(where H13 is the cell the date is in)

Now i would like the cell to change orange 3months before the year is out
and red when the year is up, but i have been unable to do it thus far.

if anyone could help that would be much appreciated

thanks


vicky

But please understand that Mike's formula for the year is looking for the
end of the calendar year (which is one interpretation of "when the year is
up"), not a year from the original date.
--
David Biddulph

"Mike H" wrote in message
...
Vicky

For the year

=(YEAR(A1)-YEAR(TODAY()))0

and month
=((A2)-TODAY())90

"Vicky" wrote:

Hi,

I am currently trying to change the cells different colours when certain
dates are added and time frames are reached. So far i have got the cells
changing to green when a date is added and once the date is a year old it
turns to red using the below formula

=AND(ISNUMBER(H13),H13<=(TODAY()-365))

(where H13 is the cell the date is in)

Now i would like the cell to change orange 3months before the year is out
and red when the year is up, but i have been unable to do it thus far.

if anyone could help that would be much appreciated

thanks

vicky