I work at a language school and I have a column giving the students expected arrival date.

I want to set conditional formatting to:

Highlight in red any date that is older than 1 week

Highlight in Green any date that is in the next 4 weeks in the future.

This will show students who haven't turned up & also those coming in the next 4 weeks.

  • 1
    You can't do that without the use of Google Apps Script.
    – Jacob Jan
    Mar 27, 2013 at 14:12

1 Answer 1


With New Google Sheets please try Custom formula is:

  1. =today()-a1>7 with Background red and Range: to suit
  2. =and(A1-today()>0,A1-today()<28) with Background green and same Range:
  3. =A1-today()>=28 with Background white and same Range:

Your Answer

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

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