Excel Formatting: Change colour based on date

Hi Experts,

I'm managing a spreadsheet that has a list of servers with the warranty expiration date (Date format: YYYY-MM-DD). How can I add a rule that will change the text font colour to red if the warranty date has expired, , change the text font colour to orange if the warranty expiration date is approaching in the next 6 months, and finally green if the warranty is still valid for at least 6 months?

I believe this is done by creating a new formatting rule (Conditional Formatting > New Rule > Select "Use a formula to determine which cells to format) but I'm not sure what the formula's should be.

Any ideas?

Thanks!

A.
AngealAsked:
Who is Participating?
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.

NBVCCommented:
Assuming dates are in column A, beginning at A2, select the cells to colour starting at same row, and use these rules:

Red:   =AND(A2<>"",A2<TODAY())

Yellow: =AND(A2>=Today(),A2<=EDATE(TODAY(),6))

Green: =AND(A2>=EDATE(TODAY(),6),A2<=EDATE(TODAY(),12))
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
AngealAuthor Commented:
Hi NBVC,

Thanks for your help. I The dates are in the "I" column starting with "I2". I modified the above to reflect this.

Unfortunately, it didn't work - RED included 2 dates that weren't quite yet expired. No Yellow (I changed it to orange) - and some were green - but not all.

The first screenshot shows how it should look - the second is how the rules are configured, in case I did it wrong.

A,ResultsRules
0
NBVCCommented:
Isn't January 23, 2014 in the past (therefore expired)?

And none of those dates are within the next 6 months, so none should be yellow (according to how I read your initial request).

Can you please review your original request and check to see if what you actually want reflects that?
0
Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

AngealAuthor Commented:
Thanks again NBVC!
0
NBVCCommented:
Your welcome.

If you desire a different output, just let me know the logic and I can revise the CF formulas for you.
0
Harry LeeCommented:
NBVC, I don't think the Green conditional format need the AND part.

As long as =A2>=EDATE(Today(),6) it should meet the requirement.
0
NBVCCommented:
Harry, you could be right depending on how you read it I suppose.

I read it as turn green if it is good for at least an extra 6 months after the "yellow" 6-month range....  so I figured Angeal would want essentially to mark everything out to 12 months with colour and leave anything later uncoloured (black).....

I did reply to Angeal asking if the desired output is different, that I would help revise my initial conditions.
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 Excel

From novice to tech pro — start learning today.