Regexmatch if, and, and date combined formula

Question

I am having issues trying to fix an older formula that I used found in this post: Regematch if, and, and date combined forumula

The issue I am having is that now they changed the "last promotion" or or "f" column to say "never" instead of being blank and it made it so the coding will not work for an Rct being promoted to Cdt.

Current formula:

=ARRAYFORMULA(IF((((REGEXMATCH(LOWER(B2:B31), "rct"))*(TODAY()>C2:C31+20))+
          ((REGEXMATCH(LOWER(B2:B31), "cdt"))*(TODAY()>F2:F31+44))+
          ((REGEXMATCH(LOWER(B2:B31), "pvt"))*(TODAY()>F2:F31+74)))*
           (REGEXMATCH(LOWER(D2:D31), "2 weeks ago|1 week ago|day|hour|minute"))*
           (REGEXMATCH(LOWER(E2:E31), "2 weeks ago|1 week ago|day|hour|minute")),
           "Y", "N"))

The error I am getting:

Function ADD parameter 1 expects number values. But 'Never' is a text and cannot be coerced to a number.

I don't understand why I am getting this error as to my eyes the formula is not looking in the "F" column for Rct, just Cdt and Pvt. If you delete "Never" from the "F" column the coding works fine

Here is a test google sheet doc: https://docs.google.com/spreadsheets/d/14d-XT0xlAOj4gbHLtYYio2dJGBieFHuwvVLx8lkALl0/edit?usp=sharing

Any help of how I could fix this coding would be greatly appreciated.

The expected output is Y or N. In the test sheet if you delete "Never" from F2:F4 the formula works correctly. I am trying to get the formula to still work but with the word "Never" being there without throwing an error. — BrettG, Jan 08 '21 at 21:07

score 1 · Answer 1 · answered Jan 08 '21 at 21:06

use:

=ARRAYFORMULA(IF((((REGEXMATCH(LOWER(B2:B31), "rct"))*(TODAY()>C2:C31+20))+
                  ((REGEXMATCH(LOWER(B2:B31), "cdt"))*(TODAY()>IFERROR(1*F2:F31)+44))+
                  ((REGEXMATCH(LOWER(B2:B31), "pvt"))*(TODAY()>IFERROR(1*F2:F31)+74)))*
                   (REGEXMATCH(LOWER(D2:D31), "2 weeks ago|1 week ago|day|hour|minute"))*
                   (REGEXMATCH(LOWER(E2:E31), "2 weeks ago|1 week ago|day|hour|minute")),
                   "Y", "N"))

score 1 · Accepted Answer · answered Jan 08 '21 at 21:29

I'm not sure how to adjust all those regexmatch()'s but here's what I would do to solve the problem. As ever, you can read about all functions by looking them up here:

=ARRAYFORMULA(IF(C2:C="",,IF(MMULT((TODAY()>{C2:C+20,N(F2:F)+{44,74}})(B2:B={"rct","cdt","pvt"}),{1;1;1})(MMULT(N(REGEXMATCH(D2:E,"2 weeks|1 week|day|hour|minute")),{1;1})=2),"Y","N")))

Also, i'm pretty sure all you'd need to fix the other one is to N() your date ranges which would turn any text into the number 0 and thus automatically create a true for whether today was "after" it or not.

Regexmatch if, and, and date combined formula

2 Answers2