Excel自定义dd/mm/yyyy HH:MM:SS格式验证：如何禁止时间自动进位？

原时间列有效性验证代码如下，仅允许输入HH:MM:SS格式的时间：

With sh.ListObjects(1).ListColumns(8).DataBodyRange.Validation
  .Add Type:=xlValidateTime, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="0:00:00", Formula2:="23:59:59"
  .ErrorMessage = "Enter time as hh:mm:ss"
End With

现需改为支持dd/mm/yyyy HH:MM:SS格式的日期时间验证，修改后的代码使用十进制验证类型：

sh.ListObjects(1).ListColumns(9).DataBodyRange.NumberFormat = "dd/mm/yyyy hh:mm:ss"

With sh.ListObjects(1).ListColumns(9).DataBodyRange.Validation
  .Add Type:=xlValidateDecimal, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="01/01/2000 0:00:00", Formula2:=Date & " " & "23:59:59"
  .ErrorMessage = "Date time must be in format dd/mm/yyyy hh:mm:ss with date after 01/01/2000 and time between 00:00:00 and 23:59:59."
End With

目前代码可限制日期不早于2000-01-01，但存在问题：输入如25:00:00的时间时，Excel会自动将日期进一天并将时间转为01:00:00，不会触发验证错误。需要确保时间部分必须严格在00:00:00至23:59:59之间。

解决方案 #

由于Excel会自动解析超出24小时的时间并调整日期，单纯的数值范围验证无法阻止这种行为。需使用自定义公式验证，同时检查日期范围和时间部分的数值区间：

sh.ListObjects(1).ListColumns(9).DataBodyRange.NumberFormat = "dd/mm/yyyy hh:mm:ss"

With sh.ListObjects(1).ListColumns(9).DataBodyRange.Validation
    .Delete ' 清除原有验证规则
    ' 添加自定义公式验证
    .Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, _
        Formula1:="AND(VALUE(A1)>=DATE(2000,1,1), VALUE(A1)<=TODAY()+TIME(23,59,59), MOD(VALUE(A1),1)>=0, MOD(VALUE(A1),1)<1)"
    .ErrorMessage = "请输入格式为dd/mm/yyyy HH:MM:SS的日期时间，日期需晚于2000-01-01，时间需在00:00:00至23:59:59之间。"
    .IgnoreBlank = True ' 可选：允许空白单元格
End With

代码说明 #

• VALUE(A1)：获取单元格存储的日期时间十进制数值（Excel中日期为整数，时间为小数部分）
• DATE(2000,1,1)：固定2000年1月1日的数值，确保日期不早于此
• TODAY()+TIME(23,59,59)：限制日期不超过当天23:59:59
• MOD(VALUE(A1),1)：提取时间部分的小数，验证其在0（00:00:00）到1（23:59:59）之间
• AND()：只有所有条件同时满足时，验证才通过

注意：公式中的A1是相对引用，Excel会自动适配列表中的每个单元格，无需手动修改引用位置。

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