Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 565
  • Last Modified:

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.
0
Angeal
Asked:
Angeal
  • 4
  • 2
1 Solution
 
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
 
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
Industry Leaders: 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!

 
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

Featured Post

Industry Leaders: 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!

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now