F003-1 計算有補上班日的工作日-NetWorkdaysPlus

Excel提供幾個跟工作天有關的函數, 常用的是 NetWorkdays 和 Workday. NetWorkdays 可以計算兩個日期之間的工作天數, 而 Workday 是計算某一日期之前或後幾個工作日的日期.

以下的範例說明這兩個函數的用法. 我們在 B4 儲存格裡面用NETWORKDAYS函數計算兩個日期之間的工作日. 2010年全年的工作日有261天. 這個函數可以帶入假日清單做為第三個參數, 在加入假日清單之後, B5 儲存格傳回的結果是 252個工作天. 在 WORKDAY 的範例裡面, B8儲存格正確地傳回 2/22 為下一個工作日.

在台灣比較麻煩的是幾乎每年都有補上班的日子, 在 Excel 的函數裡並沒有辦法做到. 2010年的2/16星期六是補上班日, 但是 WORKDAY函數沒有辦法考慮到這一點.

我們先試做一個工作表, 再將這個工作表的流程改為 VBA 函數. 要直接做 WORKDAY函數比較困難, 我們從 NetWorkdays 開始.

NetWorkdays 簡單的作法是將 總天數 - 周末天數 - 假日天數 + 補上班日數. 但 Excel 會檢查假日清單裡的日期是不是周末, 如果是周末的話並不會扣除. 所以我們在實作的時候也要考慮到假日和補上班日是否重複計算.

B5 總天數: 兩個日期直接相減是相隔的天數, 再加一就是包含前後日期的天數
B10 周末天數: 周末天數的計算比較複雜. 我們需要計算的是

1. 總共有多少個完整的星期, 每個完整的星期會有兩個周末日
2. 在完整的星期之外, 這段日期區間還多出幾天
3. 再根據首日的星期數和多出的天數得到會多出多少個周末

用 WEEKDAY 可以得知開始日是星期幾, 第二個參數2表示星期一傳回值是 1, 星期二傳回2, ... 星期六傳回6, 星期日傳回7. 如果B7+B9的值大於6, 表示多出的幾天會遇到周末(請用筆實際試算一下). 多出的周末不能超過兩天, 也不能超過多出的天數 (請思考一下為什麼).

最後我們使用 SumProduct (請參考A0010 使用 SumProduct 函數統計多重條件的合計)計算在假日清單中非周末的天數, 以及補上班日中周末的天數. 我們就可以得到 B13 的結果了.

將這個試算表改為 VBA.

可以直接在工作表中使用 NetWorkdaysPlus 這個函數.

下載: 範例檔案