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?

1 Answer 1


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()


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


=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)
  • I'm getting a VALUE error with datevalue; U.S. spreadsheet. (RegEx works like a charm. Thank you.)
    – Felix
    Oct 26, 2015 at 22:00
  • The issue I found was resolved by changing "datavalue" with an "a" at the end of "data" with "datevalue"presenting an "e" at the end of "date".
    – Felix
    Oct 28, 2015 at 2:41
  • Whoops, a typo. Corrected.
    – user79865
    Oct 28, 2015 at 2:43
  • would you please share the link of the sheet so that I could understand the way of applying the rule. This is really helpful rule i was actually looked for. Thanks in advance. Nov 21, 2017 at 5:04

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.