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

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

"Vikki" wrote:

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

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:

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

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.
--
David Biddulph

"Stefi" wrote in message
...
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:

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