Solved

Excel Formatting: Change colour based on date

Posted on 2014-02-13
7
550 Views
Last Modified: 2014-02-13
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.
0
Comment
Question by:Angeal
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
7 Comments
 
LVL 23

Accepted Solution

by:
NBVC earned 500 total points
ID: 39856806
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
 

Author Comment

by:Angeal
ID: 39856925
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
 
LVL 23

Expert Comment

by:NBVC
ID: 39856939
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Closing Comment

by:Angeal
ID: 39856956
Thanks again NBVC!
0
 
LVL 23

Expert Comment

by:NBVC
ID: 39856959
Your welcome.

If you desire a different output, just let me know the logic and I can revise the CF formulas for you.
0
 
LVL 12

Expert Comment

by:Harry Lee
ID: 39857015
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
 
LVL 23

Expert Comment

by:NBVC
ID: 39857038
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

Featured Post

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question