【VBA】【EXCEL】工作日_节假日

Option Explicit 函数一计算两个日期之间的工作日天数 用法CalcWorkDays(开始日期, 结束日期) 示例CalcWorkDays(A1,B1) Function CalcWorkDays(startDate As Date, endDate As Date) As Long Dim i As Date Dim workCount As Long Dim isHoliday As Boolean 交换日期确保开始日期≤结束日期 If startDate endDate Then Dim tempDate As Date tempDate startDate startDate endDate endDate tempDate End If workCount 0 遍历每一天判断是否为工作日 For i startDate To endDate isHoliday False 1. 判断是否为法定节假日2026年 If Is2026Holiday(i) Then isHoliday True 2. 判断是否为周末周六、周日 ElseIf Weekday(i, vbMonday) 5 Then 额外判断周末是否为补班补班则不算节假日 If Not Is2026MakeUpWork(i) Then isHoliday True End If End If 非节假日 → 工作日计数1 If Not isHoliday Then workCount workCount 1 End If Next i CalcWorkDays workCount End Function 函数二根据起始日期工作日天数计算结束日期 用法CalcEndDate(开始日期, 工作日天数) 示例CalcEndDate(A1,B1) Function CalcEndDate(startDate As Date, workDays As Long) As Date Dim currentDate As Date Dim count As Long currentDate startDate count 0 循环向后查找直到凑够指定工作日 Do While count workDays 判断当天是否为工作日 If IsWorkDay(currentDate) Then count count 1 达到目标天数直接返回避免多算一天 If count workDays Then Exit Do End If currentDate currentDate 1 Loop CalcEndDate currentDate End Function 核心工具函数判断某一天是否为工作日 Private Function IsWorkDay(checkDate As Date) As Boolean 节假日 → 不是工作日 If Is2026Holiday(checkDate) Then IsWorkDay False 周末 不是补班 → 不是工作日 ElseIf Weekday(checkDate, vbMonday) 5 And Not Is2026MakeUpWork(checkDate) Then IsWorkDay False 其余情况 → 工作日 Else IsWorkDay True End If End Function 2026年法定节假日判断官方安排 Private Function Is2026Holiday(d As Date) As Boolean Select Case d 元旦 Case #1/1/2026# 春节 Case #1/22/2026# To #1/28/2026# 清明节 Case #4/4/2026# To #4/6/2026# 劳动节 Case #5/1/2026# To #5/5/2026# 端午节 Case #6/19/2026# To #6/21/2026# 中秋节 Case #9/25/2026# To #9/27/2026# 国庆节 Case #10/1/2026# To #10/7/2026# Is2026Holiday True Case Else Is2026Holiday False End Select End Function 2026年补班日判断周末上班 Private Function Is2026MakeUpWork(d As Date) As Boolean Select Case d 春节补班 Case #1/18/2026#, #1/29/2026# 劳动节补班 Case #4/26/2026#, #5/9/2026# 端午节、中秋节、国庆节补班 Case #9/20/2026#, #10/10/2026# Is2026MakeUpWork True Case Else Is2026MakeUpWork False End Select End Function