求助：Google Sheets年度订阅付款日期计算及近期清单生成

需求说明 #

• 表格记录月度/年度重复订阅信息
• 需要在新工作表生成未来60天内的活跃付费订阅付款清单
• 要求：仅包含有效（Active）订阅，按付款日期由近及远排序

已实现的月度订阅付款日期公式 #

月度订阅的「Next Payment Date」计算公式（H列为月度账单日）：

={"Next Payment Date"; ArrayFormula(IF(H2:H<>"",EOMONTH(TODAY()-7,0)+H2:H, ))}

注：TODAY()-7用于避免月末/月初跨期时的计算偏差，确保取到正确的账单月。

年度订阅付款日期计算解决方案 #

年度订阅的核心问题是匹配正确年份：若今年的账单日已过，需自动计算明年的日期；若未过则用今年的日期。假设「Subscription Type」列（G列）标记订阅类型，「Annual Billing Day」列（I列）为年度账单日，通用付款日期公式如下：

={"Next Payment Date"; 
  ArrayFormula(
    IF(G2:G="Monthly", 
      IF(H2:H<>"", EOMONTH(TODAY()-7, 0)+H2:H, ), 
      IF(G2:G="Annual", 
        IF(I2:I<>"", 
          IF(DATE(YEAR(TODAY()), MONTH(I2:I), DAY(I2:I)) < TODAY(), 
            DATE(YEAR(TODAY())+1, MONTH(I2:I), DAY(I2:I)), 
            DATE(YEAR(TODAY()), MONTH(I2:I), DAY(I2:I))
          ), 
          ""
        ), 
        ""
      )
    )
  )
}

公式逻辑拆解 #

• 月度订阅：沿用原有逻辑，基于上月末日期加账单日生成付款日
• 年度订阅：
  1. 用DATE(YEAR(TODAY()), MONTH(I2:I), DAY(I2:I))生成今年的账单日期
  2. 对比该日期与今日：若已过期则年份+1，取明年的账单日；否则保留今年日期

生成未来60天活跃订阅清单 #

假设原数据在「Subscriptions」工作表，新工作表中使用QUERY函数提取符合条件的记录（假设K列为订阅状态，L列为计算好的「Next Payment Date」）：

=QUERY(Subscriptions!A:L, 
  "SELECT A, B, C, D, E, F, G, H, I, J, K, L 
   WHERE L >= TODAY() AND L <= TODAY()+60 AND K='Active' 
   ORDER BY L ASC", 
  1
)

筛选规则 #

• 仅保留付款日期在今日至未来60天内的记录
• 仅保留状态为「Active」的有效订阅
• 按付款日期升序排序（即由近及远排列）

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