Regematch if, and, and date 组合论坛 [英] Regematch if, and, and date combined forumula

问题描述

我需要帮助试图找出组合谷歌表格公式

I need help trying to figure out a combination google sheets formula

If 
    Cell B5 contains "Rct"
And
    today's date is 30 days after the date defined in cell C5
And
    if Cell D5 and D6 both says "2 weeks ago" or "1 week ago" or contains the words "day" "hour" or "minute"

(不确定是否可能，但如果单元格 D5 和 D6 中的文本是绿色"和/或黄色"，则不是所有这些词)

(not sure if possible but instead of the all those words could if be if the text in cell D5 and D6 are the color "green" and or "Yellow")

Then
    Cell H5 will say "Y" if the above conditions are met or "N" if they aren't

以下公式适用于我上面的要求

The following formula works for what I asked above

=ARRAYFORMULA(IF((REGEXMATCH(LOWER(B5:B29), "rct"))*
             (TODAY()>C5:C29+30)*
             (REGEXMATCH(LOWER(D5:D29), "2 weeks ago|1 week ago|day|hour|minute"))*
             (REGEXMATCH(LOWER(E5:E29), "2 weeks ago|1 week ago|day|hour|minute")), 
             "Y", "N"))

然而，我是在谷歌表格上做这些编码的新手，我虽然我可以自己添加到该公式中而不会打扰其他人.

However I am new to doing this coding stuff on the google sheets and I though I could add to that formula myself without troubling someone else.

有什么方法可以添加到该公式中，以便

Is there any way to add to that formula so that

If
    Cell B5:B28 contains "cdt"
and
    today's date is 60 days after the date defined in cell F5:F29
And
   if Cell D5 and D6 both says "2 weeks ago" or "1 week ago" or contains the words "day" "hour" or "minute"
Then
    Cell H5 will say "Y" if the above conditions are met or "N" if they aren't

以及

If
   Cell B5:B28 contains "pvt"
and
   today's date is 90 days after the date defined in cell F5:F29
And
   if Cell D5 and D6 both says "2 weeks ago" or "1 week ago" or contains the words "day" "hour" or "minute"
Then
    Cell H5 will say "Y" if the above conditions are met or "N" if they aren't

推荐答案

将其粘贴到H5:

=ARRAYFORMULA(IF((REGEXMATCH(LOWER(B5:B), "rct"))*
                 (TODAY()>C5:C+30)*
                 (REGEXMATCH(LOWER(D5:D), "2 weeks ago|1 week ago|day|hour|minute"))*
                 (REGEXMATCH(LOWER(E5:E), "2 weeks ago|1 week ago|day|hour|minute")), 
                 "Y", "N"))

---

更新:

=ARRAYFORMULA(IF((((REGEXMATCH(LOWER(B5:B), "rct"))*(TODAY()>C5:C+30))+
                  ((REGEXMATCH(LOWER(B5:B), "cdt"))*(TODAY()>C5:C+60))+
                  ((REGEXMATCH(LOWER(B5:B), "pvt"))*(TODAY()>C5:C+90)))*
                   (REGEXMATCH(LOWER(D5:D), "2 weeks ago|1 week ago|day|hour|minute"))*
                   (REGEXMATCH(LOWER(E5:E), "2 weeks ago|1 week ago|day|hour|minute")),
                   "Y", "N"))

这篇关于Regematch if, and, and date 组合论坛的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！