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 bookcaseCell 2:10/23 Put books backCell 3:10/24 Repair bookcase, 11/2 Send cat to vetCell 4:12/6 Sell catCell 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?
How do I conditionally format a cell based on a date within a string of text?
google spreadsheets;google apps for work
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()+1Not 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)