vendredi 8 janvier 2021

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.

Aucun commentaire:

Enregistrer un commentaire