2021-07-20 计算两个日期间的工作日天数VBA函数

Excel自带的NETWORKDAYS函数不支持指定为工作日,故自己写个自定义函数

Function WorkdayCount(ByVal startDate As Date, ByVal endDate As Date, Optional holidays As Range = Nothing, Optional workDays As Range = Nothing)
'计算两个日期间的工作日天数，参数1为起始日，参数2为终止日，参数三为指定休息日所在单元格（可选参数），参数四为指定工作日所在的单元格（可选参数）
    Dim dictWorkday As Object, dictHoliday As Object, iCount&, rng As Range, date_ As Date
   
    '设置休息日字典
    Set dictHoliday = CreateObject("scripting.dictionary")
    If Not holidays Is Nothing Then  '如果指定了特定休息日则加入字典
        For Each rng In holidays
            dictHoliday(rng.Value) = ""
        Next
    End If
   
    '设置工作日字典
    Set dictWorkday = CreateObject("scripting.dictionary")
    If Not workDays Is Nothing Then   '如果指定了特定工作日则加入字典
        For Each rng In workDays
            dictWorkday(rng.Value) = ""
        Next
    End If
       
    iCount = 0  '计数器初始值=0,可省略
   
    For date_ = startDate To endDate
        '如果为指定工作日则计数器+1。注意:如果指定工作日与指定休息日有重复值,会优先判断为工作日
        If dictWorkday.Exists(date_) Then
            iCount = iCount + 1
        ElseIf dictHoliday.Exists(date_) Then
            '跳过休息日,如需统计休息日可增加变量
        Else
            If Weekday(date_, vbMonday) < 6 Then iCount = iCount + 1
        End If
    Next
   
    WorkdayCount = iCount  '返回计数器结果
   
End Function