1

I'd like to conditionally format cells that will go red if it's past its expiration, go yellow if it's within 3 months of expiring, to remain green if date is clear of expiry.

For example:
today's date is the 15/10/15

  • Cell has written in 26/09/19 (this should be green)
  • Another cell has 10/11/15 entered - so within the 3 months (this should now be yellow)
  • Another cell has 13/10/15 entered - now expired (this should now be red)

Is it possible to do it this way?

Improve this question

1 Answer 1

Reset to default
2
  1. Select the range to which the rule should apply: in my example it is G1:G8.
  2. Enter the custom formula criterion for red color: =G1<TODAY(). Note that this says "G1" because the formula is stated as it applies to the upper left corner of the range. It will be automatically adjusted for other cells in the range according to the usual rules for relative references.
  3. Click "add another rule", enter the formula for yellow, e.g., =G1<TODAY()+90
  4. Click "add another rule", enter the formula for green, e.g., =G1>=TODAY()+90

If several rules apply, those listed later override the earlier ones.

The result:

cells                              rules

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.