Angeal
asked on
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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?
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?
ASKER
Thanks again NBVC!
Your welcome.
If you desire a different output, just let me know the logic and I can revise the CF formulas for you.
If you desire a different output, just let me know the logic and I can revise the CF formulas for you.
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.
As long as =A2>=EDATE(Today(),6) it should meet the requirement.
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.
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.
ASKER
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,