如何在Excel中识别日期范围含周末/节假日及解决WORKDAY函数问题

问题核心 #

需要判断指定日期范围（含起止日期）是否包含周末或节假日，是则标记「Fail」，否则标记「Pass」。原使用WORKDAY的公式逻辑存在缺陷，无法正确识别节假日，同时需要实现硬编码节假日的需求。

一、修复基于工作日计数的判断逻辑 #

原WORKDAY公式的逻辑错误：WORKDAY(B2, C2-B2, 节假日)是计算从开始日期往后推C2-B2个工作日的日期，而非判断日期范围内是否存在节假日。改用NETWORKDAYS.INTL函数更准确，它直接计算两个日期间的工作日数（自动排除周末和指定节假日）。

引用单元格区域的节假日公式 #

=IF(NETWORKDAYS.INTL(B2,C2,1,H$2:H$11)=C2-B2+1,"Pass","Fail")

• 参数说明：
  • NETWORKDAYS.INTL(B2,C2,1,H$2:H$11)：计算B2到C2之间的工作日数，1代表默认周末为周六、周日，H$2:H$11是存放节假日的单元格区域
  • C2-B2+1：日期范围的总天数（含起止日期）
  • 逻辑：如果工作日数等于总天数，说明无周末/节假日，标记「Pass」；否则标记「Fail」

二、硬编码节假日的实现 #

无需引用单元格区域，直接将节假日用DATE函数组成数组传入公式：

=IF(NETWORKDAYS.INTL(B2,C2,1,{DATE(2023,12,25),DATE(2024,1,1),DATE(2024,2,10)})=C2-B2+1,"Pass","Fail")

• 把需要的节假日用DATE(年,月,日)格式写入大括号{}内，多个日期用逗号分隔即可。

三、更直观的条件判断公式 #

如果需要直接检查起止日期是否为周末，或范围内是否存在节假日，可使用以下公式：

引用节假日区域版本 #

=IF(OR(WEEKDAY(B2,2)>5,WEEKDAY(C2,2)>5,SUMPRODUCT(--(COUNTIF(H$2:H$11,ROW(INDIRECT(B2&":"&C2)))>0))>0),"Fail","Pass")

硬编码节假日版本 #

=IF(OR(WEEKDAY(B2,2)>5,WEEKDAY(C2,2)>5,SUMPRODUCT(--(ISNUMBER(MATCH(ROW(INDIRECT(B2&":"&C2)),{DATE(2023,12,25),DATE(2024,1,1)},0))))>0),"Fail","Pass")

• 逻辑拆解：
  • WEEKDAY(B2,2)>5：判断开始日期是否为周六（6）或周日（7）
  • WEEKDAY(C2,2)>5：判断结束日期是否为周末
  • SUMPRODUCT(...)>0：检查日期范围内是否存在节假日，存在则返回True
  • OR(...)：只要满足任一条件，标记「Fail」，否则「Pass」

示例验证 #

针对你的示例表格，使用上述公式后结果如下：

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