請假時數計算問題

接下來想要分享在工作場合遇到的一個問題，這次我試著用不同的函式組合，計算請假時數。一起來看看怎麼做！

問題定義

我們要設計一個算出員工請假時數的方法。

員工會提供要請假的起始日期、時間，還有請假的終止日期、時間：

員工會這樣填入表格：

我們要設法算出綠色那邊的請假時數。

上班時間的規定是這樣的：

• 上班時間是 9 點到 18 點。
• 週六、週日不上班。
• 國定假日依政府的行政機關辦公日曆而定，遇國定假日不上班、有補班日也不上班。
• 12 點到 13 點是午休時間。
• 一天的總工作時數應是 8 小時。

這邊附上行政機關辦公日曆給你參考：

圖片來源：行政院人事行政總處

好，怎麼算呢？在這邊分享我的想法，歡迎跟我交流！

先製作國定假日的表

首先要先把日曆轉成表格，讓等等的函式可以讀取國定假日。這邊沒有什麼技巧，就只是謄打上去過後，確保日期是正確格式即可。

計算起始日至終止日之間有哪些日

之後我起了一張新的工作表：

這邊把起始日期、起始時間、結束日期、結束時間簡單用相對參照，把資料帶過來。

（這邊以 2024 的 6 月 6 日到 21 日為範例）

我想知道起始日期到終止日期之間，有哪些日子是國定假日、哪些是週末（六、日），所以準備了這樣的表：

在「展開日期」欄，列出起始日期到結束日期之間的日期：

=SEQUENCE(DAYS(C5, C2) + 1, 1, C2, 1)

接下來判斷左邊的日期有哪些是週末、哪些是國定假日、哪些是平日。

=ArrayFormula(IF(B10:B <> "", IFNA(
    IFS(WEEKDAY(B10:B, 2) >= 6, "週末", 
        ISNUMBER(MATCH(date, '2024 台灣假期'!B:B, 0)), "國定假日"), "平日"), ""))

因為我們只會算平日（工作日）的工作時數，所以如果該日期是週末或國定假日，我們就不會計算休假時數了。

找第一天、最後一天

接著，我在右邊起了一個新的表格，用 QUERY 取得平日的日期。

=QUERY(B9:C, "SELECT B WHERE C = '平日' LABEL B '工作日'")

如果要計算時數的話，我想到要先把第一天、中間的天數和最後一天拆開計算，這麼一來：

• 第一天的請假時間即是起始時間～18:00
• 中間的請假時間即是 09:00～18:00
• 最後一天的請假時間即是 09:00～終止時間

所以，拿這次的例子來看：

• 第一天是 6 月 6 日的 14:00～18:00。
• 中間是 6 月 7 日到 20 日的 09:00～18:00。
• 最後一天則是是 6 月 21 日的 09:00～12:00。

為了達成這個，首先要找到工作日的第一天和最後一天。我繼續往右邊新增欄位：

經過 QUERY 篩選過後的第一個日期絕對是第一天，所以我這邊就寫了簡單的 IF 來標示：

=IF(E10 <> "", TRUE, "")

再來是找最後一天的工作日，我們用 OFFSET 來找。如果 OFFSET 下一列是空白的話，那那一列就會是最後一天了。

=ArrayFormula(IF(E10:E <> "", IF(OFFSET(E10:E, 1, 0) <> "", "", TRUE), ""))

再來新增兩欄，我們要用剛剛提到的拆開計算的邏輯，用來計算該日的開始時間與結束時間：

找開始時間、結束時間

首先算開始時間。「第一天」那欄是 TRUE 的話，代表該列是第一天，那麼就以員工申請請假的起始時間起計，否則就回傳一天的上班時間，也就是 9 點。

=ArrayFormula(IF(E10:E <> "", IF(F10:F = TRUE, C3, TIMEVALUE("09:00")), ""))

這邊開始時間是以小數點顯示。這邊如果看不習慣的話，可以更改格式讓開始時間換成以時與分顯示：

再來是結束時間。「最後一天」那欄是 TRUE 的話，代表該列是最後一天，那麼就以員工申請請假的終止時間起計，否則就回傳一天的上班時間，也就是 18 點。

=ArrayFormula(IF(E10:E <> "", IF(G10:G = TRUE, C6, "18:00"), ""))

午休時間有無

還差一點點。我們要計算每列的開始時間與結束時間內，是否有跨到午休時間，如果有的話，就扣除。我這邊做了一個新欄，算出跨到午休時間有多少。

=MAP(H10:H, I10:I, LAMBDA(startTime, endTime,
 IF(startTime <> "", 
   IF(AND(endTime >= TIMEVALUE("12:00"), startTime <= TIMEVALUE("13:00")),
     (MIN(endTime,   TIMEVALUE("13:00")) - MAX(startTime, "12:00")) * 24, 0), 
 "")))

計算休假時數

再來就可以算每日的休假時數了。這邊多加一欄「休假時數」：

=ArrayFormula(IF(E10:E <> "", (I10:I - H10:H) * 24 - J10:J, ""))

最後就是把這欄簡單用 SUM 加總起來，就可以得到答案了。

=SUM(K10:K)

這是我想到的方法，不過或許還有更簡單的計算方式！

回顧一下這邊的步驟有：

• 先準備國定假日的表
• 計算起始日到終止日共有哪些日子，把國定假日和週末篩選掉
• 把日期拆成第一天的、最後一天的、中間經過日，再分別計算每天的起始日與終止日
• 考量午休時間的狀況，若有，需要扣除
• 最後加總，即是答案

我把我的過程放在這個試算表，歡迎來參考！

如果你喜歡這次的文章，歡迎你透過這些方法支持我：

• 按下愛心、按下儲存
• 留言告訴我你的想法
• 加入喜特先生的官方沙龍，即時看到我發布的教學
• 付費訂閱喜特先生的官方沙龍，加入每月小額訂閱方案
• 追蹤喜特先生的 Facebook
• 按這邊小額贊助我的創作！

想要看更多文章的話，歡迎來到我的 Notion 頁面找找有沒有你需要的資源喔！

我是喜特先生，Mr. Sheet，我們下個教學見！