3 color conditional formatting formulas rules

[Brew]

How do I create 3 color conditional formatting formulas for the following rules?:

if today is 15 days or greater before the day in the cell then green
if today is between 14 and 7 days before the day in the cell then yellow
if today is within 6 days to zero days before the day in the cell then results
anything is clear
x8:x21
5/10/2012
5/14/2012
5/21/2012
6/11/2012
6/12/2012
6/12/2012
6/12/2012
6/20/2012
6/22/2012
6/23/2012
7/4/2012
7/5/2012
7/8/2012
7/12/2012

 

[delaneyjm]

Not sure if the third condition got cut off, but try this:

Excel Workbook
	X
8	05/10/2012
9	05/14/2012
10	05/21/2012
11	06/11/2012
12	06/12/2012
13	06/12/2012
14	06/12/2012
15	06/20/2012
16	06/22/2012
17	06/23/2012
18	07/04/2012
19	07/05/2012
20	07/08/2012
21	07/12/2012
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
X8	1. / Formula is =AND(X8>TODAY()+7,X8	Abc
X8	2. / Formula is =X8>=TODAY()+15	Abc

 

[TheCman81]

I don't think you need a condition for it to remain clear

=IF(TODAY()-X8>=15,1,0) - Green

=IF(AND(TODAY()-X8<=14,TODAY()-X8>=7),1,0) - Yellow

 

[Brew]

the condition is red:
if today is within 6 days to zero days before the day in the cell then Red

 

[TheCman81]

=IF(AND(TODAY()-X8<=6,TODAY()-X8>=0),1,0) -Red

 

[Brew]

I get the following error message while in the conditional formatting module:

"
you cannot use relative references in conditional formatting criteria for color scales, data bars, and icon sets."

excel 2007
I went to conditional formatting - new rules - format all cells based upon there values - 3 color style formate -
type formula, then i entered the red formula in first value field, yellow in 2nd value field and green in 3rd value field

 

[TheCman81]

Sorry I should have said..... You need to go to New Rule - Use a formula to determine format

Also once you've have done your condition, make sure you apply it to your range

 

[Brew]

please send my previous message, i got an error message

 

[Brew]

I get the following error message while in the conditional formatting module:

"
you cannot use relative references in conditional formatting criteria for color scales, data bars, and icon sets."

Below is the procedure i used
excel 2007
I went to conditional formatting - new rules - format all cells based upon there values - 3 color style format -
type formula, then i entered the red formula in first value field, yellow in 2nd value field and green in 3rd value field

 

[TheCman81]

You DONT use conditional formatting - new rules - format all cells based upon there values

Use

conditional formatting - new rule - Use a formula to determine format