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?


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 '15 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 '15 at 2:41
  • Whoops, a typo. Corrected. – user79865 Oct 28 '15 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. – Shazzad Kabir Nov 21 '17 at 5:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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