基于历史发薪日查询当月双周发薪日期的Excel公式异常问题

问题背景 #

单元格A1为历史发薪日，需通过公式生成当月双周发薪的日期列表，当前使用的公式如下：

• B1：=DAY(A1+(14*INT((TODAY()-A1)/14)))
• C1：=DAY(A1+14+(14*INT((TODAY()-A1)/14)))
• D1：=LET(x,DAY(A1+28+(14*INT((TODAY()-A1)/14))),IF(OR(x>DAY(EOMONTH(TODAY(),0)),x<=C1),"-",x))

昨日（非23号时）结果符合预期：

今日（23号）结果异常：

公式问题根源 #

核心问题是仅提取日期的日数，丢失了月份上下文：

1. B1/C1的逻辑漏洞：公式计算的是基于今日的最近发薪日，但用DAY()只取日数后，无法区分该日期属于当月还是下月。比如今日是23号，计算出的下一个发薪日是下月6号，DAY()返回6，但公式误以为这是当月日期，导致C1显示6。
2. D1的判断失效：用提取的日数x和C1的日数（6）对比x<=C1，但6是下月日期，当月的20号自然大于6，就错误显示了20号——但20号早于今日23号，根本不是当月后续的发薪日。

修正方案 #

不要单独提取日数，先计算完整的发薪日期，判断是否属于当月后再处理：

逐个单元格修正 #

• B1（当月最近的上一个发薪日）：

  =LET(last_pay,A1+(14*INT((EOMONTH(TODAY(),-1)+1-A1)/14)),IF(last_pay>=EOMONTH(TODAY(),-1)+1,DAY(last_pay),DAY(last_pay+14)))
  

• C1（下一个发薪日）：

  =LET(next_pay,DATE(YEAR(TODAY()),MONTH(TODAY()),B1)+14,IF(next_pay<=EOMONTH(TODAY(),0),DAY(next_pay),"-"))
  

• D1（再下一个发薪日）：

  =LET(next_next_pay,DATE(YEAR(TODAY()),MONTH(TODAY()),C1)+14,IF(AND(next_next_pay<=EOMONTH(TODAY(),0),next_next_pay>TODAY()),DAY(next_next_pay),"-"))
  

批量生成更高效 #

用SEQUENCE一次性生成所有可能的发薪日，再筛选出当月的，直接填充到B1、C1、D1等单元格：

=LET(
    start_date,A1+(14*INT((EOMONTH(TODAY(),-1)+1-A1)/14)),
    all_pays,SEQUENCE(3,,start_date,14),
    valid_pays,FILTER(all_pays,(all_pays>=EOMONTH(TODAY(),-1)+1)*(all_pays<=EOMONTH(TODAY(),0))),
    IFERROR(DAY(valid_pays),"-")
)

这个公式会自动生成当月所有符合条件的双周发薪日，不足3个时自动显示"-"。

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