CODEWITHSUNDEEP
regexif-statementgoogle-sheetsgoogle-sheets-formulaarray-formulas
BrettG
BrettG

Reputation: 41

Regexmatch if, and, and date combined formula

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.

Upvotes: 0

Views: 132

Answers (2)

MattKing
MattKing

Reputation: 7783

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.

Upvotes: 1

player0
player0

Reputation: 1

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"))

Upvotes: 1

Related Questions