Using CF to change colour in cell based on date?

S

Steve

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.
 
F

FSt1

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))....color green
conditon 3
cell value is...greater than .....= F11....color yellow

regards
FSt1
 
F

FSt1

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
 
S

Steve

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))
 
F

FSt1

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top