Conditional Formatting-Traffic Lights

[JABWootton]

Cell N13 has a formula in which calculates a Date given certain Criteria
Trying to get Cell N13 to change colours according to Date in Cell N13
If Date in Cell N13 is less than Today then it goes Red
If Date in Cell N13 is more than 120 Days Away then it goes Green
If Date in Cell N13 is less than 120 days away then it goes Orange

Can seem to get simple formula to work
Thanks in Anticipation

 

[GlennUK]

You haven't given the formula of N13. I assume you are entering formulae in the Conditional Formatting conditions too, but you haven't shown those either.

 

[onlyadrafter]

Hello,

is it

Condition 1 (Formula is)

=$N$13<TODAY()

Condition 2 (Formula is)

=$N$13>TODAY()+120

Condition 3 (Formula is)

=$N$13<TODAY()+120

format as required

 

[JABWootton]

Sorry
Formula in Cell is
=IF(AND(B13="Y",ISNUMBER(SEARCH("GCD",C13)),ISNUMBER(J13),OR((K13=1),(K13=2))),J13+180,"")

Adds 180 days to Date entered in Cell J13 if Y in Cell B13 and "GCD" is somewhere in Cell C13

Hope to enter formulas in 3 Conditions but only entering following

=(N13<TODAY()) Turns cell Red

=(N13>=TODAY()) Turns Cell Green

Unable to Do the one that will change it Orange
Think that the 2 formulas alreay entered are wrong thus causing problem
Hope this Helps

 

[Richard Schollar]

And watch out because if the date is exactly 120 days away you won't get any colour (conditional) formatting returned. Hence, you might like to amend OaD's final formula to:

$N$13<=TODAY()+120

Richard

 

[JABWootton]

Hello,

Condition 1 (Formula is)

=$N$13<TODAY()

Condition 2 (Formula is)

=$N$13>TODAY()+120

Condition 3 (Formula is)

=$N$13<TODAY()+120

format as required

Thanks
Dosent Seem to work
Cell Changes Green OK
Also Changes to Orange OK
But not to red

Assume Reading Correct
Condition 1 Changes Cell to Red
Condition 2 Changes Cell to Green
Condition 3 Changes Cell to Orange

Do I need to enter them in this order in the 3 Conditions

 

[JABWootton]

It works
Thanks
Forgot to Take " Out when copying

Thanks Again
Much Appreciated