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 holidaysdictHoliday(rng.Value) = ""NextEnd If'設置工作日字典Set dictWorkday = CreateObject("scripting.dictionary")If Not workDays Is Nothing Then '如果指定了特定工作日則加入字典For Each rng In workDaysdictWorkday(rng.Value) = ""NextEnd IfiCount = 0 '計數器初始值=0,可省略For date_ = startDate To endDate'如果為指定工作日則計數器+1。注意:如果指定工作日與指定休息日有重復值,會優先判斷為工作日If dictWorkday.Exists(date_) TheniCount = iCount + 1ElseIf dictHoliday.Exists(date_) Then'跳過休息日,如需統計休息日可增加變量ElseIf Weekday(date_, vbMonday) < 6 Then iCount = iCount + 1End IfNextWorkdayCount = iCount '返回計數器結果End Function

總結

以上是生活随笔為你收集整理的2021-07-20 计算两个日期间的工作日天数VBA函数的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。