Google-sheets – Conditional formatting based on comparison with today’s date and also with a future date

conditional formattinggoogle sheets

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?

Best Answer

  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

Related Topic