Conditional Formating

[Guest]

I have set up in conditonal formating to change cell patterns to be a certain
color based on the date. The below conditional formating formula works, now
what I need to for the words (Red, Yellow, or Green to automatically appear
in the cell based on the color i.e if cell color is Red I what the word Red
to appear in the cell. How can I do this?

=F2<=TODAY() (pattern color Red)
=F2<=TODAY()+5 (pattern color Yellow)
=F2<=TODAY()+25 (pattern color Green)

 

[greg7468]

Hi Roy,

=IF(F2<=TODAY(),"RED",IF(F2<=TODAY()+5,"YELLOW",IF(F2<=TODAY()+25,"GREEN")))

HTH.

Greg

 

[Guest]

Greg,

Thanks that worked.

Now I have another issue. The spread sheet I have created is basically an
action plan (column A Date Opened, column B Issue, column C Root Cause,
column D Action Plan, column E Responsible, column F Date Due, column G Date
Complete, column H Status R/Y/G) is my issue, the date due column could be
out as far as 30 + days (i.e. date open is 5/25/05, date due could be
6/25/05) when I put in the 6/25 date column H says it is false, when infact
it should be Green. What can I do to correct either the cell formula or the
formula in CF or both?

Thanks for your help,
Roy

 

[greg7468]

Roy,
It returns a value of false because it is outside the +25 days from
today rule you have set.

I assume you are only really interested when the cell changes to yellow
then red, whilst it's green it doesn't matter how many days away the due
date is.

If this is the case why not just change the formula to

=IF(F2<=TODAY(),"RED",IF(F2<=TODAY()+5,"YELLOW","GREEN"))

This will be green for anything over 5 days away from today.

If this does not work for you, what do you want it to return if the due
date is 30 days away from today.

HTH

Greg.

 

[Guest]

Greg,

Thanks that worked.