Solved

Excel Formatting: Change colour based on date

Posted on 2014-02-13
7
526 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
  • 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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 

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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

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…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

803 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