Google Sheets – Conditional Formatting Based on Date Within Text String

conditional formattinggoogle sheetsgoogle-apps

Suppose today is 13 October 2015. I want to apply conditional formatting rules to four cells in Google Sheets:

Cell 1:

10/22 Remove cat from bookcase

Cell 2:

10/23 Put books back

Cell 3:

10/24 Repair bookcase, 11/2 Send cat to vet

Cell 4:

12/6 Sell cat

Cell 1 should be formatted one way ([first] date is before today), Cell 2 should be formatted a second way ([first] date is today), Cell 3 should be formatted a third way (first date is tomorrow) and Cell 4 ([first] date is not within the last week, today nor tomorrow).

How do I do this?

Best Answer

The command 

=datevalue(regexextract(A1, "[0-9]{1,2}/[0-9]{1,2}"))

extracts the first date-looking part from a string and interprets it as a date (if this is possible according to the current locale's convention; "13/2 buy a dog" will throw an error).

You can then set multiple rules with custom formulas involving the comparison of the above with today():

Before today:

=datevalue(regexextract(A1, "[0-9]{1,2}/[0-9]{1,2}")) < today()

Today:

=datevalue(regexextract(A1, "[0-9]{1,2}/[0-9]{1,2}")) = today()

Tomorrow:

=datevalue(regexextract(A1, "[0-9]{1,2}/[0-9]{1,2}")) = today()+1

Not within last week, today or tomorrow: 

=or(datevalue(regexextract(A1, "[0-9]{1,2}/[0-9]{1,2}")) < today()-7, datevalue(regexextract(A1, "[0-9]{1,2}/[0-9]{1,2}")) > today()+1)
Related Topic