excel自動填入工作日, 並跳過自訂的休假日

excel有一個很好用的公式叫[WORKDAY], 可以簡單的自動填入工作日, 並跳過自訂的休假日


用途 : 可用於排班 (例如每日的工作站), 或填表單 (例如只有工作日需要填寫的表單)等等


預計完成的效果如下圖

自動跳過週六及週日, 並且跳過預訂的公休日

首先, 我們必須先在A2輸入起始的日期, 如果是今天, 可以用Ctrl + ; 快速填入今天的日期 (輸入法必須是英數, 如果是在中文輸入法的狀況下, 是無法使用這個快速鍵的), 本例輸入的是2016/05/05

接著, 在A3填入公式,

點選插入函數的按鍵

選日期與時間, 點選WORKDAY函數

Start_date: 
就是開始日期, 也就是我們的A2

Days:
就是開始日期之後的第幾天, 因為我們是要從2016/5/5的隔天開始計算, 所以這裡我們輸入 1 , 但是如果我們要計算的是2016/5/5之後的第100天, 那就填入100

Holidays:
就是我們自訂的, 週六週日以外的特殊假日, 如果我們的表單單純只要排除六日, 那這裡可以空白不填, 因為我們在H2到 H4有自訂了幾天公休日, 因此我們輸入H2:H4

但是因為我們之後需要下拉公式, 而公休日的表格位置是不變的, 所以我們需要用到 [絕對儲存格] 的概念, 在儲存格的位置加上 $ , 就表示之後下拉公式時, 這個參數是不變的, 因此在這裡, 我們要輸入的是$H$2:$H$4, 因為不管哪一天, 公休日就是這三天 (如果沒有加上$, 下拉公式時, 這裡會變成H3:H5, 那它對應的位置就不是我們放置公休日的表單位置了)

Tip : 輸入公休日的位置時, 不能把標題 [公休日] 也加進去, 也就是說不能輸入 H1:H4, 因為H1是文字, 會造成公式錯誤, 無法計算

以上參數輸入完畢之後, 按確定, 就完成了, 之後再把公式下拉複製即可

2024/3/31回答下面網友的問題, 

若是我們的固定假日不是週六、週日, 我們可以使用另一個函數WORKDAY.INTL, 多了一個參數"weekend"可以設定

Start_date: 就是開始日期, 也就是我們的A1

Days: 就是開始日期之後的第幾天, 若是隔天, 就填入1

Weekend  ：是指定何時為假日的數字。依照網友的需求, 指定週日為假日的數字為"11", 其它選項如下表

Holidays: 就是我們自訂的, weekend以外的特殊假日

2024/04/21回答下面網友的問題, 

"如果是服務業沒有固定休假日，怎麼更改公式？"

如果是每天都要上班, 只有特定日期休假的話, excel並沒有一個單獨的函數可以直接套用, 我所能想到的函數寫法如下:

=IF(COUNTIF($C$2:$C$6,A2+1)>0,A2+2,A2+1)

在這個函數組合裡, $C$2:$C$6是用來填寫休假日的範圍(C2到C6), 若您想填寫的日期較多, 例如C2到C20, 請將以上函數改為=IF(COUNTIF($C$2:$C$20,A2+1)>0,A2+2,A2+1)

A2為班表起始日期, A3開始就放我上面寫的函數, 將函數往下拉即可

關於這個函數寫法的解釋如下:

=IF(COUNTIF($C$2:$C$6,A2+1)>0,A2+2,A2+1)

先看內層的函數, 這一層的主要目的是要確認A2的隔天是否為休假日, COUNTIF($C$2:$C$6,A2+1)

COUNTIF在Excel中用於計算"符合指定條件的儲存格數量"。它有兩個參數:

1. 範圍: 要檢查的區域範圍,在這裡是$C$2:$C$6。$符號讓這個範圍固定不變,不會因為公式下拉而改變檢查範圍

2. 條件: 要計數的條件,這裡是A2+1 (即A2的隔天, 2024/4/22)

這個COUNTIF函數的作用是:在$C$2:$C$6範圍內,計算有多少個儲存格的值等於A2+1

所以, 若在$C$2:$C$6範圍內, 有2024/4/22, 算出來的結果會>0, 就表示2024/4/22為休假日

接著再套用外層的if函數, 若2024/4/22為休假日(>0)時, A3的日期就會用A2+2(即2024/4/23), 若不是, 則為A2+1(即2024/4/22)

希望這樣有解決到您的問題

2024/6/1回答下面網友的問題, 

如果固定休週三及週日要如何改函數, 一樣可以用"WORKDAY.INTL"函數, 修改其中一個引數即可

將起始日期打在A2, 再將以下函數寫入A3即可"=WORKDAY.INTL(A2,1,"0010001")"

修改的引數及結果如下圖

Weekend引數若加上雙引號時, 可以用來自訂自己要的固定工作日, 以7位數來代表一週的每一天, "0"代表工作日, "1"代表非工作日, 例如:

"0000011"代表週六及週日為非工作日

"0010001"代表週三及週日為非工作日

特別留意的是, 記得要在字串的前後加上雙引號", 不然結果會回傳為錯誤

2024/6/24回答下面網友的問題, 

為何日期後打上(週X)後，後面會全變成錯誤? 

因為沒看到檔案, 所以只能用猜的, 我猜測是因為直接用打字的方式打上"(週X)", excel若要讓函數計算正確, 要讓日期顯示星期幾, 要從格式去改, 不能直接打上去, 示範如下

在自訂儲存格格式中, 將格式設定為yyyy/m/d(aaa)或yyyy/m/d(aaaa), 在excel中, "aaa"代表的日期格式為"週X", "aaaa"代表"星期X", 如下圖