Conditional formatting on dates


I want to use conditional formatting in an excel document to highlight:

Out of date dates - red
Will expire next calendar month (or in 30 days) - orange
In date - green

I have managed the red and green but the orange doesn't seem to work no what order I put the rules in.

I currently have my dates in format dd-yyyy as a specific date is not really relevant to my data

Any help greatly appreciated!

Many Thanks
Leigh MacpheeAsked:
Who is Participating?

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

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.

KoenChange and Transition ManagerCommented:
look at attached sheet if this is what you need...
What are you using as your formulas?
Rob HensonFinance AnalystCommented:
With a sample file we can apply some rules for you.

As a starter though you need to think of dates as numbers as that is how Excel stores them.

The function =TODAY() will return the current date.

Therefore, with a date in A1:
A1>TODAY() is TRUE if in the future
A1<TODAY() is TRUE if in the past
AND(A1>TODAY(),A1<TODAY()+30)  is TRUE if within the next 30 days
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Leigh MacpheeAuthor Commented:
Thanks for all your replies.

Koen I have tried to apply your rules but it only seems to be picking up the orange and green values (apologies if I'm just being really dumb).

I have attached a sample file.

To try and explain, I am setting up a database which will contain links to a whole load of documents; these documents will need updating periodically.

I have formula '=DATE(YEAR(C4) + 1, MONTH(C4), DAY(C4))' in column 'I' as the majority of documents need reviewing every year however I am happy to get rid of this and enter all the review dates manually if it will work better! I then want the formatting to turn it red if the date has passed orange if date is in the next 30 days and green if it's still in date.

I am also happy to change column 'I' to dd-mm-yyyy if this will work better.

Many Thanks,
Leigh MacpheeAuthor Commented:
Whoops sorry forgot to actually upload file............
Rob HensonFinance AnalystCommented:
See attached with updated rules:

Green:   =AND(I4<>"",I4>TODAY()+30)
Amber:  =AND(I4<>"",I4>TODAY(),I4<TODAY()+30)
Red:      =AND(I4<>"",I4<=TODAY())
Applies to range:   =$I$4:$I$50    for all of them, adjust the 50 as required.

Also adjusted the Applies to Range on the rule on column J to $J$4:$J$50 as this was piecemeal and got rid of all the other attempts.

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
Leigh MacpheeAuthor Commented:
Rob Henson - spot on! Thank you very much!

Thanks again for all your quick answers everyone - much appreciated :)
Rob HensonFinance AnalystCommented:
Thanks for the feedback.

BTW the NOW funcion gives current date and time whereas TODAY only gives date.
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 Excel

From novice to tech pro — start learning today.