traffic light formula

V

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
 
E

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
 
S

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:
 
D

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.
 

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