Excel数据验证嵌套条件失效问题排查与解决请求

我有一列用于输入员工项目结束日期，需验证该日期是否在项目周期内，或员工是否因离职无法在该日期在岗。若不符合要求，需同时设置条件格式高亮单元格和数据验证提示错误，且数据验证需在项目/员工日期变更后仍能显示错误。

目前条件格式已正常工作，使用公式：=OR($I2>ProjectEndDate,IF(NOT(ISBLANK(StaffEndDate)),$I2>StaffEndDate,FALSE))，当输入日期晚于项目结束日期或员工离职日期时会高亮单元格。

但数据验证使用公式=AND($I2<=ProjectEndDate,IF(NOT(ISBLANK(StaffEndDate)),$I2<=StaffEndDate,TRUE))却失效：例如项目结束日期为2023/3/31，输入2023/4/1时，单元格会高亮但无数据验证错误提示。若移除IF条件，使用=AND($I2<=ProjectEndDate, TRUE)则数据验证正常。

经拆解，当StaffEndDate为空时，公式会简化为与正常验证相同的逻辑，但仍无法生效，请问该公式失效的原因及解决方法？

Excel数据验证的公式逻辑对空值的处理规则和条件格式存在差异：

• 当StaffEndDate为空时，原公式中IF分支返回的独立逻辑值TRUE，在AND函数组合时会触发Excel隐性类型转换异常，导致整个公式的判断逻辑失效。
• 若ProjectEndDate或StaffEndDate是全局命名范围而非行级相对引用，也可能导致数据验证无法匹配当前行的判断条件。

推荐两种经过验证的修改方案：

方案1：重构逻辑，避免独立逻辑值 #

将公式调整为直接通过OR处理空值分支，确保AND的每个参数都是明确的判断表达式：

=AND($I2<=ProjectEndDate,OR(ISBLANK(StaffEndDate),$I2<=StaffEndDate))

逻辑说明：

• 必须满足输入日期不晚于项目结束日期
• 同时满足「员工离职日期为空」或「输入日期不晚于员工离职日期」

方案2：确保命名范围的行级引用（若需逐行验证） #

如果ProjectEndDate和StaffEndDate是每行独立的单元格（比如对应某列的同行单元格），需将命名范围设置为相对行引用。例如，若员工离职日期在H列，命名范围应设为=Sheet1!$H2（而非全局固定单元格），再配合方案1的公式使用。

验证步骤 #

1. 删除原数据验证规则，重新添加并应用修改后的公式
2. 输入测试值：项目结束日期为2023/3/31，输入2023/4/1，确认弹出数据验证错误提示
3. 清空员工离职日期，再次输入超出项目日期的数值，确认错误提示正常触发

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