Conditional Formatting: Excel 2007

I have a column called DEADLINE (Column B).  I'm trying to use conditional formatting to color cells based on the deadline.  

I want the cell to turn red if the dealdine is equal to today's date or it has already passed, turn yellow if the deadline is within 10 days of today's date, or green if today's date is more than 10 days away.
Here's what I tried but it's not working:
=B3>=(TODAY()+11)   formatted to turn Green
=B3<=(TODAY()+10)   formatted to turn Yellow
=B3<=(TODAY())         formatted to turn Red

Any help would be appreciated.
dplearningAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dplearningAuthor Commented:
Thanks Tripyre but this won't work becuse this only works for values in the cell.  I actually need it to calculate a formula.
0
barry houdiniCommented:
in conditional formatting the conditions are apied in order, so with the coditions you have all past dates will be yellow

Try switching conditions 2 and 3 (you can move them up and down with the arrows)

Regards, barry
0
Acronis Data Cloud 7.8 Enhances Cyber Protection

A closer look at five essential enhancements that benefit end-users and help MSPs take their cloud data protection business further.

irudykCommented:
Try changing the Yellow condition such that it is:
Cell Value between =TODAY()+1 and =TODAY()+10
(See attached screen shot)

ExcelCF.JPG
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rory ArchibaldCommented:
You can also just set the Stop if True option for condition 2.
0
barry houdiniCommented:
Hello Rory,
I'm not sure how that would work here. If the conditions are in the order given by the asker then past dates will still be formatted yellow.
In any case, if the formatting of the conditions is mutually exclusive, e.g. assuming green fill, yellow fill, red fill then "stop if true" effectively applies anyway even if not explicitly set.
What always annoys me about conditional formatting in Excel 2007 is that the first condition you set up actually defaults to the last condition on the list, so if the conditions are applied in the order shown they'll actually be placed in reverse order an that should work as required......
regards, barry
0
Rory ArchibaldCommented:
Barry,
You're quite right - brain fart on my part. Definitely pub time for me... :)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Applications

From novice to tech pro — start learning today.