學不完．教不停．用不盡

網友問到 Excel 的問題：如何在一個日期區間中計算工作天數、放假天數？

參考下圖，因為每個人遇到的狀況不同，當要計算工作天數時，你可能要考量到星期六、星期日和放假日，也要考量到補班日，該如何處理？

 

【公式設計與解析】

1. 不含六日的工作天數

儲存格C2：=NETWORKDAYS(A2,B2)

利用 NETWORKDAYS 函數，只要給予起始和終止的日期，即可計算日期區間中扣除星期六日的天數。

如果你的 Excel 版本沒有 NETWORKDAYS 函數，則可以改用 SUMPRODUCT 函數：

儲存格C2：=SUMPRODUCT(1*(WEEKDAY(ROW(INDIRECT("A" & N(A2) & ":A" & N(B2))),2)<6))

"A" & N(A2) & ":A" & N(B2)：將二個日期的數值組成一個儲存格範圍，例如：A42370:A42552。其中 N 函數可以將一個日期傳回其代表的數值。

INDIRECT("A" & N(A2) & ":A" & N(B2)：利用 INDIRECT 函數將上式轉換為真實的儲存格參照位址。

ROW(INDIRECT("A" & N(A2) & ":A" & N(B2)))：將上式置入 ROW 函數，轉換為列號範圍，例如：A42370:A42552→ROW(A42370:A42552)，在 SUMPRODUCT 函數中可以表示為 42370, 42371, 42372, ..., 42551, 42552 組成的陣列。

WEEKDAY(ROW(INDIRECT("A" & N(A2) & ":A" & N(B2))),2)<6：在 WEEKDAY 函數中利用參數『2』，得到傳回值小於 6 者(表示星期一至星期五)的 TRUE／FALSE 陣列。

1*(WEEKDAY(ROW(INDIRECT("A" & N(A2) & ":A" & N(B2))),2)<6)：利用『*1』，將上式中的 TRUE／FALSE 陣列轉換為 1／0 陣列。

最後，透過 SUMPRODUCT 函數加總，即為所求。

 

2. 不含六日、不含假日的工作天數

儲存格D2：=NETWORKDAYS(A2,B2,$G$3:$G$16)

在 NETWORKDAYS 函數置入第 3 個參數，其為放假日的儲存格範圍。

如果你的 Excel 版本沒有 NETWORKDAYS 函數，則可以改用 SUMPRODUCT 函數：

SUMPRODUCT(($G$3:$G$16<=B2)*($G$3:$G$16>=A2))：

求在儲存格G3:G16的放假日中，介於儲存格B2和儲存格A2兩個日期之間的個數。

儲存格D2：=SUMPRODUCT(1*(WEEKDAY(ROW(INDIRECT("A" & N(A2) & ":A"
& N(B2))),2)<6))-SUMPRODUCT(($G$3:$G$16<=B2)*($G$3:$G$16>=A2))

 

3. 不含六日、不含假日、含補班日的工作天數

SUMPRODUCT(($G$19:$G$21<=B2)*($G$19:$G$21>=A2))：

求在儲存格G19:G21的補班日中，介於儲存格B2和儲存格A2兩個日期之間的個數。

儲存格E2：=NETWORKDAYS(A2,B2,$G$3:$G$16)+SUMPRODUCT
(($G$19:$G$21<=B2)*($G$19:$G$21>=A2))

如果你的 Excel 版本沒有 NETWORKDAYS 函數，則可以改用 SUMPRODUCT 函數：

儲存格E2：=SUMPRODUCT(1*(WEEKDAY(ROW(INDIRECT("A" & N(A2) & ":A" &
N(B2))),2)<6))-SUMPRODUCT(($G$3:$G$16<=B2)*($G$3:$G$16>=A2))+
SUMPRODUCT(($G$19:$G$21<=B2)*($G$19:$G$21>=A2))