traffic light formula

[Vikki]

I am trying to create a traffic light formula in excel 2003. I want a cell B2
at the beginning of my spreadsheet to be green, if the date in cell K2 is a
minimum of 3 months away, amber if it is less than 3 months away and red if
it equal to or the expiry date.

Hoping you can help!

Vikki

 

[Eduardo]

Hi,
use conditional formating entering the formula as follow. I assume that in
K2 you have a date
=today()-K2>=90 choose color amber
=today()-k2<90 choose color red

 

[Stefi]

Apply Conditonal formatting for B2 with these conditions:
=DATEDIF(TODAY(),$K$2,"m")>=3 for green
=AND(DATEDIF(TODAY(),$K$2,"m")<3,TODAY()<$K$2) for amber
=TODAY()>=$K$2 for red.

Regards,
Stefi




„Vikki†ezt írta:

 

[David Biddulph]

As I'm sure Stefi realises, you don't need
=AND(DATEDIF(TODAY(),$K$2,"m")<3,TODAY()<$K$2)
but can cope with just
=TODAY()<$K$2
as the
=DATEDIF(TODAY(),$K$2,"m")>=3
cases have already been trapped out.

Similarly the
=TODAY()>=$K$2
test isn't needed, as the
=TODAY()<$K$2
cases have already been trapped out.

CF doesn't go on to a later test if an earlier condition has already been
satisfied.