重新组合if，and和date合并论坛 [英] Regematch if, and, and date combined forumula

问题描述

我需要帮助找出一个组合的Google表格公式

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

但是，我是第一次在Google表格上执行此编码工作，尽管我可以自己添加该公式，而不会困扰其他人.

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

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