I would like to use Conditional Formating to change the colour of one cell
depending on the date of another cell. What I mean is say I put a date (e.g.
31 Mar 10) in cell F11 and want to add 5 years onto that date and have that
cell (cell G11) give me the new date with the cell colour of green. As the
time counts down have the cell turn yellow, say one year before the end of
the 5 year date, and then turn red once the 5 year date has expired. What
formulas do I use to produce this.

hi
in G11
condition 1
cell value is....less than.....=F11.... color red
conditon 2
cell value is....greater than or equal
to...=DATE(YEAR(F11)+1,MONTH(F11),DAY(F11))....col or green
conditon 3
cell value is...greater than .....= F11....color yellow

regards
FSt1

"Steve" wrote:

I would like to use Conditional Formating to change the colour of one cell
depending on the date of another cell. What I mean is say I put a date (e.g.
31 Mar 10) in cell F11 and want to add 5 years onto that date and have that
cell (cell G11) give me the new date with the cell colour of green. As the
time counts down have the cell turn yellow, say one year before the end of
the 5 year date, and then turn red once the 5 year date has expired. What
formulas do I use to produce this.

hi
in G11
condition 1
cell value is...less than....=F11.....color red
condition 2
cell value is...greater than or equal to...
=DATE(YEAR(F11)+1,MONTH(F11),DAY(F11)).....color green
condition 3
cell value... is greater than...=F11...color yellow

regards
FSt1


"Steve" wrote:

I would like to use Conditional Formating to change the colour of one cell
depending on the date of another cell. What I mean is say I put a date (e.g.
31 Mar 10) in cell F11 and want to add 5 years onto that date and have that
cell (cell G11) give me the new date with the cell colour of green. As the
time counts down have the cell turn yellow, say one year before the end of
the 5 year date, and then turn red once the 5 year date has expired. What
formulas do I use to produce this.

FSt1,

Is this what you mean,

Green - =DATE(YEAR(F12)+4,MONTH(F12),DAY(F12))
Yellow - =DATE(YEAR(F12)+4,MONTH(F12),DAY(F12)+1)
Red - =DATE(YEAR(E10),MONTH(E10)+60,DAY(E10)+1))

"FSt1" wrote:

hi
in G11
condition 1
cell value is...less than....=F11.....color red
condition 2
cell value is...greater than or equal to...
=DATE(YEAR(F11)+1,MONTH(F11),DAY(F11)).....color green
condition 3
cell value... is greater than...=F11...color yellow

regards
FSt1


"Steve" wrote:

I would like to use Conditional Formating to change the colour of one cell
depending on the date of another cell. What I mean is say I put a date (e.g.
31 Mar 10) in cell F11 and want to add 5 years onto that date and have that
cell (cell G11) give me the new date with the cell colour of green. As the
time counts down have the cell turn yellow, say one year before the end of
the 5 year date, and then turn red once the 5 year date has expired. What
formulas do I use to produce this.

hi
no.
i mean exactly what i posted.
if g11 is less than f11, date as expired.....red.
if g11 is greater than f11, ok......yellow
but if g11 is greater thna f11 by one year, even better...green.

regards
FSt1

Regards
FSt1

"Steve" wrote:

FSt1,

Is this what you mean,

Green - =DATE(YEAR(F12)+4,MONTH(F12),DAY(F12))
Yellow - =DATE(YEAR(F12)+4,MONTH(F12),DAY(F12)+1)
Red - =DATE(YEAR(E10),MONTH(E10)+60,DAY(E10)+1))

"FSt1" wrote:

hi
in G11
condition 1
cell value is...less than....=F11.....color red
condition 2
cell value is...greater than or equal to...
=DATE(YEAR(F11)+1,MONTH(F11),DAY(F11)).....color green
condition 3
cell value... is greater than...=F11...color yellow

regards
FSt1


"Steve" wrote:

I would like to use Conditional Formating to change the colour of one cell
depending on the date of another cell. What I mean is say I put a date (e.g.
31 Mar 10) in cell F11 and want to add 5 years onto that date and have that
cell (cell G11) give me the new date with the cell colour of green. As the
time counts down have the cell turn yellow, say one year before the end of
the 5 year date, and then turn red once the 5 year date has expired. What
formulas do I use to produce this.