如何让MS Excel及指定公式在输入错误日期时返回错误值

• 选中你要限制日期输入的单元格或单元格区域
• 点击顶部「数据」选项卡，选择「数据验证」（部分老版本叫「数据有效性」）
• 在弹出的窗口中，「允许」下拉选择「自定义」，在公式栏输入=ISDATE(A1)（A1为选中区域的首个单元格，Excel会自动适配区域内其他单元格）
• 切换到「出错警告」标签页，「样式」选择「停止」，填写提示标题和内容（例如“日期不合法，请重新输入”）
• 点击「确定」完成设置。此后输入非日期或逻辑错误的日期（如2023年2月31日）时，Excel会直接弹出错误提示，阻止无效输入。

从截图中的公式可知，原公式为=DATE(YEAR(A2),MONTH(A2)+1,0)——这个公式会自动“修正”错误日期（比如输入2023年2月31日，Excel会自动转为2023年3月3日，公式会返回3月31日）。要让它在遇到逻辑错误的日期时返回错误值，分两种场景处理：

场景1：单元格为日期格式（Excel自动修正错误日期） #

此时错误日期会被Excel自动转换为合法日期，我们需要验证该日期是否为“原生合法”日期，修改后的公式如下：

=IF(DATE(YEAR(A2),MONTH(A2),DAY(A2))=A2,DATE(YEAR(A2),MONTH(A2)+1,0),#VALUE!)

逻辑说明：用DATE函数重新拼接当前单元格的年、月、日，若拼接后的日期与原单元格一致，说明是合法日期；若不一致，则说明是Excel强制修正的错误日期，直接返回#VALUE!错误。

场景2：单元格为文本格式（输入的是文本型错误日期） #

若单元格为文本格式，输入“2023-2-31”这类错误日期文本时，原公式会报错，我们可以让错误提示更规范，使用以下公式：

=IF(ISNUMBER(DATEVALUE(A2)),DATE(YEAR(DATEVALUE(A2)),MONTH(DATEVALUE(A2))+1,0),#VALUE!)

逻辑说明：用DATEVALUE函数将文本转换为日期（Excel中日期本质是数字），若转换成功则说明是合法日期，执行原逻辑；若转换失败，则说明是错误日期文本，返回#VALUE!错误。

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