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?


1 Answer 1




Second entry is a JS regular expression. You can build it with a tool like regexr.

  • Ahhh, I somehow never saw regexmatch - that did it! Thanks!
    – Alex
    Sep 5, 2019 at 12:44

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.