基于发布时长计算周数、月份及年份的公式优化问题

现存问题 #

• 匹配逻辑错误：原公式对“11 months”的匹配写了带单引号的'11' months，但实际内容是无引号的“11 months”，导致匹配失败，错误触发最后的周数计算；年份类内容（如“11 years”）也因匹配规则不严谨，无法正确返回空值。
• 公式冗余低效：多层IF嵌套结构复杂，新增时间规则或数据行时需手动修改，维护成本高且不可持续。

优化公式 #

假设时长数据在N列，参考日期在B列，在目标列首行输入以下公式即可实现整列自动计算：

=ARRAYFORMULA(IF(ROW(N:N)=1, "周数", IF(N:N="", "", 
  LET(
    num, REGEXEXTRACT(N:N, "\d+"),
    unit, REGEXEXTRACT(N:N, "(day|week|month|year)s?"),
    SWITCH(
      unit,
      "day", IF(num<=7, 0, 1),
      "week", num*1,
      "month", IF(num<=5, num*4, ""),
      "year", "",
      WEEK(B:B, 2) - WEEK(DATE(YEAR(B:B),1,1), 2) + 1
    )
  )
)))

公式说明 #

• 自动适配整列：ARRAYFORMULA让公式自动应用到N列所有行，新增数据无需手动调整；
• 统一提取规则：用REGEXEXTRACT分别提取时长的数字和单位（兼容单复数，比如“day”和“days”都能匹配）；
• 简化规则判断：SWITCH替代多层IF，清晰定义每种单位的处理逻辑：
  • 天数：7天及以内返回0，8-10天返回1；
  • 周数：直接返回对应的数字；
  • 月份：5个月及以内按每月4周计算，6个月及以上返回空；
  • 年份：直接返回空；
  • 未匹配到上述单位时，计算参考日期的当年周数（和原公式最后逻辑一致）。

问题修复验证 #

• “11 months”：提取到数字11和单位month，因超过5个月，返回空值；
• “11 years”：匹配到year单位，直接返回空值；
• 新增数据行：公式自动覆盖，无需手动修改行号。

内容的提问来源于stack exchange，提问作者djur