Conditional Formatting with date range

[Vergy39]

Trying to create a conditional format that will change cell color when the date in the cell is greater than today and less then 7 days from today And the word "yes" does not appear in D2. For example date in cell B2 = 2/6/2014, and today was 2/5, and D2 is blank the cell would be orange. Here is what we had but it does not work.

=AND(MEDIAN(TODAY()+1,$B2,TODAY()+7)=$B2,$D2<>”Yes”)

Any assistance is greatly appreciated.

Thanks
David V.

 

[Brianwarnock]

Why are you using Median?

Try

=AND(B2>TODAY()+1,B2<TODAY()+7,D2<>"YES")

Brian

 

[Vergy39]

Why are you using Median?

Try

=AND(B2>TODAY()+1,B2<TODAY()+7,D2<>"YES")

Brian

Thanks for the quick response. I tried your suggestion and unfortunately, it did not work. I thought it did not work because we have other rules as well and that the other rules were affecting it. I am attaching a sheet with some example data. The rules that we need are:

a.) If the date in cell B2 is Greater than today and less than 7 days from today, and the word "YES" does NOT appear in cell D2, then turn cells orange.
b.) If the date in cell B2 is less than today and the word "YES" does NOT appear in cell D2, then turn cells red.
c.) If the word "YES" DOES appear in cell D2, then turn cells Grey.
d.) If cells A2:E1000 are blank, no conditional formatting should apply.

We can get rules b and c to work, but not a and d.

Any assistance is greatly appreciated.

Thanks
David V

 

[Brianwarnock]

I am having trouble with attachments and cannot open yours, so I have created a little spreadsheet with the first three conditions applying to column A , I think that the fourth condition is a default, ie nodata no format.

Brian

 

[Brianwarnock]

Although still having trouble with attachments I did manage to get David's spreadsheet loaded, he however appears to have lost interest.

It did not work properly but I could not see anything wrong , but when I deleted the rule and redid it it worked fine.

Brian