conditional formattingdategoogle sheets
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.
You can achieve your desired results by putting a tilde, ~ in front of the asterisk as an escape character, if you put the conditional formatting rules in the order listed below.
First, create the one for orange when three asterisks occur using text contains and then specifying ~*~*~*. Select custom to pick an orange background. Then create the one for two asterisks using text contains and ~*~* picking yellow for the color. Then for the next rule create one for green with text contains and ~*. Then you can create the one with a red background using text is exactly specifying Dead and picking red for the background.
You should then see the following:
Generally: the way to refer to "this" cell is to enter notation for the upper-left corner of the range being formatted. For example, if the range is C1:C, then C1 means "this cell".
For example, formatting C1:C with custom formula
=C1 < today() - 14
will format all cells in C with dates two weeks in the past.
If the range you want to format begins with row 8, and you think you may be inserting rows above that (thus shifting the range), then this formula can be used:
=and(C1 < today() - 14, row(C1) >= 8)
The formatting will apply only to rows starting with 8, but the range being the entire column, the formula will handle insertion of rows above row 8.
Best Answer
With New Google Sheets please try Custom formula is:
=today()-a1>7with Background red and Range: to suit=and(A1-today()>0,A1-today()<28)with Background green and same Range:=A1-today()>=28with Background white and same Range: