如何修改Excel VBA日期计算代码以适配工作日节假日规则

整体实现思路 #

不要把所有计算逻辑堆在Worksheet_Change事件里，把公共的日期判断、调整逻辑抽成独立的自定义函数，后续改规则、复用到其他列都方便。 #

分场景计算规则落地 #

• 20自然日计算（AW列）：直接取对应行N列的日期值加20得到初始日期，后续走统一的无效日期调整流程即可。
• 20工作日计算（AZ列）：不要直接在N列日期上加固定天数，用逐天计数的方式实现：
  1. 初始化计数器为0，当前计算日期取N列的原始日期
  2. 循环将当前日期加1天，每加1天就判断当天是否为有效工作日（周一至周五，且不在指定节假日列表内），符合条件则计数器加1
  3. 直到计数器累计到20时停止循环，此时的日期就是20个工作日后的初始日期，再走统一无效日期调整流程即可。
• BA列按照你实际需要的天数规则算出初始日期后，同样走统一调整流程。

无效日期（周末/节假日）调整逻辑实现 #

你构思的调整方向完全可行，落地按以下步骤写即可：

1. 先统一存储节假日数据：把你列的所有指定节假日存到VBA数组或者Dictionary对象里，注意所有日期要去掉时分秒、统一转成Date类型存储，避免后续判断时因为格式不匹配漏判。
2. 写通用调整函数，入参是初始计算得到的日期，返回值是符合要求的有效工作日，判断顺序如下：
   • 先调整周末：用Weekday(待调整日期, vbMonday)判断星期，返回值为6代表是周六，直接加2天调到周一；返回值为7代表是周日，直接加1天调到周一。
   • 再调整节假日：检查调整完周末的日期是否存在于你提前存的节假日列表中，如果命中节假日，就把日期加1天，加完后必须回到周末判断步骤重新校验——因为加1天后有可能刚好落到周末，或者碰到另一个相邻的节假日，必须循环校验直到日期既不是周末、也不在节假日列表内，才能作为最终结果返回。

原有事件代码修改注意事项 #

• 代码开头先加Application.EnableEvents = False关闭事件触发，避免你给单元格赋值时反复触发Worksheet_Change造成死循环；要加错误捕获，不管代码运行是否出错，最后都要把Application.EnableEvents改回True，避免影响表格后续正常操作。
• 取N列日期值前先判断单元格内容是否为合法日期，遇到空值、文本值时直接跳过计算，避免抛出类型错误。
• 所有日期计算完成后，再统一用Format()函数转成mm/dd/yyyy HH:mm:ss格式赋值给对应单元格，不要在计算过程中做格式化，避免日期变成文本类型导致后续计算出错。

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