日期公式输出异常：给定日期求下周五时周六输入返回上周五

需求：使用Excel公式计算给定日期对应的下一个周五。

问题描述 #

当输入日期为周六时，当前公式返回上周五而非正确的下周五，示例如下：

上述两个A列结果均应为28/04/2023。

当前使用公式 #

=IF(H3986="","",(H3986+(6-WEEKDAY(H3986))))

注：H列与A列单元格均已设置为日期格式。

已尝试的修改公式 #

• IF(H3989="","",(H3989+7-WEEKDAY(H3989)))
• IF(H3989="","",(H3989+(6-WEEKDAY(H3989, 2))))
• IF(H3989="","",(H3989+(5-WEEKDAY(H3989, 16))))
• IF(H3989="","",(H3989+(5-WEEKDAY(H3989, 2))))

解决方案 #

方法一：基于WEEKDAY的通用公式 #

=IF(H3986="","",H3986 + (5 - WEEKDAY(H3986, 2) + 7) % 7)

逻辑说明

• WEEKDAY(H3986, 2)：指定周一为周1、周日为周7，此时周五对应数值为5。
• (5 - WEEKDAY(H3986, 2) + 7) % 7：通过取模运算确保得到从当前日期到下一个周五的正天数差。比如输入日期为周六（周6）时，计算得(5-6+7)%7=6，即加6天到下周五；输入为周日（周7）时，得(5-7+7)%7=5，加5天到下周五；输入为周五时，得(5-5+7)%7=7，返回下周五。

若需要当天为周五时返回当天，可调整为：

=IF(H3986="","",H3986 + IF(WEEKDAY(H3986,2)=5,0,(5 - WEEKDAY(H3986, 2) + 7) % 7))

方法二：使用WORKDAY.INTL函数（Excel 2010+适用） #

=IF(H3986="","",WORKDAY.INTL(H3986,1,"0000011"))

逻辑说明

• WORKDAY.INTL函数用于计算指定工作日之后的日期，第三个参数"0000011"表示周六、周日为休息日。
• 参数1表示往后推1个工作日，因此无论输入日期是周几，都会返回之后的第一个周五（周五输入则返回下周五）。

若需要当天为周五时返回当天，调整为：

=IF(H3986="","",IF(WEEKDAY(H3986,2)=5,H3986,WORKDAY.INTL(H3986,1,"0000011")))

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