I have a sheet that has a list of job names, and then the last failure and success of each job. The dates are listed with duration words instead of dates, ie 1 mo 12 days or 3 days 6 hr.
I want to highlight a row if either the last success OR the last failure was over a year ago. This means highlighting the row if column B or column C contains the text yr.
Example data:
job A | 1 yr 2 days | 1 day
job B | 2 mo 1 day | 1 yr 3 days
job C | 1 day | 1 mo 2 day
In this case I'd want to highlight rows 1 and 2 as both contain a date over a year old.
I tried the following formulas:
=OR($B2="yr", $C2="yr")
# doesn't recognize "yr" - presumably this is an absolute match
=OR($B2=".*yr.*", $C2=".*yr.*")
# attempted to add wildcards but it's not matching anything
The formula works great if I do an absolute match, ie 1 day will highlight rows 1 and 3, but doesn't work with pattern matches. What am I missing?
Best Answer
Try
REGEXMATCH:Second entry is a JS regular expression. You can build it with a tool like regexr.