如何通过命名区域调整IF公式，将银行假日全标记为Out of Hours？

现有如下IF语句公式：

=IF(AND(MOD(J2,1)>TIME(8,15,0),MOD(J2,1)<TIME(17,0,0)),"工作时间","非工作时间")

格式化后便于阅读的版本：

=IF(
    AND(
        MOD( J2, 1 )  >  TIME(  8, 15, 0 ),
        MOD( J2, 1 )  <  TIME( 17,  0, 0 )
    ),
    "工作时间",
    "非工作时间"
)

2023-2024财年英国银行假日日期如下：

• 2023年4月7日
• 2023年4月10日
• 2023年5月1日
• 2023年5月8日
• 2023年5月29日
• 2023年8月28日
• 2023年12月25日
• 2023年12月26日
• 2024年1月1日
• 2024年3月29日

需求：如何借助命名区域，让上述公式将所有银行假日日期（无论具体时间）都标记为“非工作时间”？

1. 创建银行假日命名区域 #

• 在工作表空白列（比如A列）输入所有银行假日日期，确保单元格格式设置为日期格式；
• 选中这些日期单元格，点击菜单栏「公式」→「定义名称」，在弹出窗口中输入名称（例如UKBankHolidays），点击确定完成命名。

2. 修改原公式 #

调整公式逻辑，优先判断日期是否属于银行假日，再校验时间区间。修改后的公式如下：

=IF(
    OR(
        COUNTIF(UKBankHolidays, INT(J2)) > 0,
        NOT(AND(
            MOD(J2, 1) > TIME(8, 15, 0),
            MOD(J2, 1) < TIME(17, 0, 0)
        ))
    ),
    "非工作时间",
    "工作时间"
)

公式说明 #

• INT(J2)：提取J2单元格中的日期部分，去除时间信息；
• COUNTIF(UKBankHolidays, INT(J2)) > 0：检查提取的日期是否存在于UKBankHolidays命名区域中，存在则判定为银行假日；
• OR()函数：只要满足「是银行假日」或「时间不在8:15-17:00区间内」任一条件，就返回「非工作时间」，否则返回「工作时间」。

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