接下來想要分享在工作場合遇到的一個問題，這次我試著用不同的函式組合，計算請假時數。一起來看看怎麼做！
問題定義
我們要設計一個算出員工請假時數的方法。員工會提供要請假的起始日期、時間，還有請假的終止日期、時間：
員工會這樣填入表格：
我們要設法算出綠色那邊的請假時數。
上班時間的規定是這樣的：
- 上班時間是 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)
這是我想到的方法，不過或許還有更簡單的計算方式！
回顧一下這邊的步驟有：
- 先準備國定假日的表
- 計算起始日到終止日共有哪些日子，把國定假日和週末篩選掉
- 把日期拆成第一天的、最後一天的、中間經過日，再分別計算每天的起始日與終止日
- 考量午休時間的狀況，若有，需要扣除
- 最後加總，即是答案
我把我的過程放在這個試算表，歡迎來參考！
如果你喜歡這次的文章，歡迎你透過這些方法支持我：
想要看更多文章的話，歡迎來到我的 Notion 頁面找找有沒有你需要的資源喔！
我是喜特先生，Mr. Sheet，我們下個教學見！