我想算一段請假時間，從週五下午請到週一早上，中間的午休和週末都不能算進去。只用結束減開始，會把整個週末也一起算了；這篇用 LET、MAP 把區間拆到每天，分別計算和上下午班段重疊的時間。
先把規則寫清楚：上午 09:00–12:00、下午 13:00–18:00，週六日休息，再排除一份自訂假日清單。這是本篇的計算條件，沒有涵蓋輪班、補班日或跨午夜班段，實際假別怎麼計算仍要依你使用的規則另外設計。
輸入開始、結束與假日清單
在 B1 寫「請假開始」、C1 寫「請假結束」，B2、C2 放帶有日期的時間：
| 儲存格 | 內容 |
|---|---|
B2 | 2026/10/16 10:00 |
C2 | 2026/10/16 11:00 |
E1 | 請假小時數 |
10 點到 11 點完整落在上午班段，手算是 1 小時。換成 11 點到 14 點，則是上午 11–12 點加下午 13–14 點，應該只算 2 小時。
J1 寫「自訂假日」，J2:J4 放下面的日期。這幾筆是範例用的日期，10/20 在本例中被指定為假日，不是在宣告真實國定假日：
| 儲存格 | 日期 | 這筆的用途 |
|---|---|---|
J2 | 2026/10/20 | 另外排除的日期 |
J3 | 2026/10/20 | 重複日期不應再扣一次 |
J4 | 2026/10/18 | 已經是週日，也不應重複扣 |
B2、C2 要是真正的日期時間數值，J 欄則放日期，不要夾帶時間。看起來像日期卻算不動時，先看 日期與時間入門，確認儲存格沒有被當成文字。
E2 的完整公式
把這條貼到 E2，輸出是以「小時」為單位的一般數值，先用數值格式看結果：
逐日計算上下午重疊時數，排除週末與J2:J4的假日
=LET(
startAt, B2,
endAt, C2,
IF(
OR(startAt="", endAt=""),
"請填開始與結束",
IF(
OR(NOT(ISNUMBER(startAt)), NOT(ISNUMBER(endAt))),
"請輸入有效日期時間",
IF(
endAt<startAt,
"結束早於開始",
ROUND(
SUM(
MAP(
SEQUENCE(INT(endAt)-INT(startAt)+1, 1, INT(startAt), 1),
LAMBDA(
dayAt,
IF(
AND(WEEKDAY(dayAt, 2)<=5, COUNTIF($J$2:$J$4, dayAt)=0),
MAX(
0,
MIN(endAt, dayAt+TIME(12, 0, 0)) -
MAX(startAt, dayAt+TIME(9, 0, 0))
) +
MAX(
0,
MIN(endAt, dayAt+TIME(18, 0, 0)) -
MAX(startAt, dayAt+TIME(13, 0, 0))
),
0
)
)
)
)*24,
8
)
)
)
)
)開頭那組 B2、C2 會得到 1。它已經乘過 24 換成小時，請別再把 E2 設成時刻格式，否則畫面會變成另一種意思。
整條比較長，我通常先確認開始、結束與假日範圍，再看下面的拆解。後面的式子片段都來自這條完整公式，閱讀時不用另外貼成小公式。
每天分開算上午與下午
LET 先把 B2 命名成 startAt，C2 命名成 endAt，後面就不用一直重寫儲存格位置。dayAt 則是當次正在處理的那一天，由 MAP 逐日傳進來。
以上午班段來說，dayAt+TIME(9,0,0) 是當天 09:00，dayAt+TIME(12,0,0) 是當天 12:00。要留下重疊時間，做兩件事：
MIN(endAt,dayAt+TIME(12,0,0))：結束不能晚於上午下班，所以拿兩者較早的時間。MAX(startAt,dayAt+TIME(9,0,0))：開始不能早於上午上班，所以拿兩者較晚的時間。
較早的結束減掉較晚的開始，就是重疊長度。最外面再包一層 MAX(0,...)，沒有重疊時就歸零，避免得到負數。
下午用同樣的方式，把班段改成 13:00–18:00，再把兩段相加。午休 12:00–13:00 沒有放進任何一段，自然就不會被算到，也不用事後猜這次應該扣幾次午休。
舉例來說，11:00–14:00 的上午交集是 11:00–12:00，下午交集是 13:00–14:00，所以是 2 小時。整段落在午休的 12:00–13:00，兩邊交集都為零。
拆出每一天，排除週末與假日
INT(startAt) 把開始時間的小數部分去掉，只留下日期；結束日期也用 INT(endAt) 取得。SEQUENCE 依這兩個日期產生每天的日期序列，從開始日走到結束日，首尾都保留。
接著 MAP 搭配 LAMBDA，把每個日期交給剛剛的兩段交集計算。這樣第一天只會留下請假開始後的部分，最後一天只會留下請假結束前的部分，中間完整的工作日則算完整班段。
每一天還要先通過這兩個條件，AND 要求兩者都成立：
WEEKDAY(dayAt,2)<=5：第二個參數用2，週一是 1、週日是 7，所以只留下週一到週五。COUNTIF($J$2:$J$4,dayAt)=0：假日清單沒有這一天，才計算班段；找到一次或重複找到，都會讓這一天歸零。
週末或假日直接回傳零，其他日子才加上午與下午的交集。SUM 加完所有日期後乘 24，把「一天的幾分之幾」換成小時。
日期時間換算成小時後，浮點數會有微小的尾數，例如保留 10 位小數仍可能出現 0.9999999999。所以末尾用 ROUND(…, 8) 收斂成 8 位；這邊只是整理數值誤差，沒有加入「不足半小時算半小時」之類的進位規則。
跨日、午休與假日案例
以下案例都沿用同一條公式與 J2:J4 的範例假日清單，只換開始、結束；每個結果的單位是小時。
| 情境 | 開始 | 結束 | 結果 |
|---|---|---|---|
| 同日一小時 | 2026/10/16 10:00 | 2026/10/16 11:00 | 1 |
| 跨午休 | 2026/10/16 11:00 | 2026/10/16 14:00 | 2 |
| 完全午休 | 2026/10/16 12:00 | 2026/10/16 13:00 | 0 |
| 超出上下班時間 | 2026/10/16 08:00 | 2026/10/16 19:00 | 8 |
| 下班後 | 2026/10/16 18:00 | 2026/10/16 19:00 | 0 |
| 週五到週一 | 2026/10/16 17:00 | 2026/10/19 10:00 | 2 |
| 首日是週末 | 2026/10/17 10:00 | 2026/10/19 10:00 | 1 |
| 末日是週末 | 2026/10/16 17:00 | 2026/10/18 10:00 | 1 |
| 全區間都是週末 | 2026/10/17 10:00 | 2026/10/18 17:00 | 0 |
| 首日是自訂假日 | 2026/10/20 09:00 | 2026/10/21 18:00 | 8 |
| 末日是自訂假日 | 2026/10/19 17:00 | 2026/10/20 18:00 | 1 |
| 中間有自訂假日 | 2026/10/19 09:00 | 2026/10/21 18:00 | 16 |
週五 17:00 到週一 10:00，只留下週五 17–18 點與週一 09–10 點，所以是 2 小時。10/19 早上到 10/21 傍晚則是週一、週三各 8 小時，週二 10/20 被假日清單排除，得到 16。
邊界與輸入不完整
公式開頭先檢查空白、是否為數字，再檢查結束有沒有早於開始。這些提示要先處理好，才不會把反向日期送進 SEQUENCE，或讓文字日期繼續往下算。
| 情境 | 開始 | 結束 | E 欄結果 |
|---|---|---|---|
| 反向區間 | 2026/10/16 11:00 | 2026/10/16 10:00 | 結束早於開始 |
| 開始空白 | （空白） | 2026/10/16 11:00 | 請填開始與結束 |
| 相同時刻 | 2026/10/16 10:00 | 2026/10/16 10:00 | 0 |
| 開始是文字 | 不是日期 | 2026/10/16 11:00 | 請輸入有效日期時間 |
| 完整上午 | 2026/10/16 09:00 | 2026/10/16 12:00 | 3 |
| 完整下午 | 2026/10/16 13:00 | 2026/10/16 18:00 | 5 |
數值檢查會擋下「不是日期」這種文字，但一般數字也可能被當成日期序號，所以資料輸入端仍要確認日期範圍合理。這條公式沒有替使用者判斷每個輸入是不是一筆合理的請假申請。
換成自己的資料
一筆申請占一列時，把開始放 B 欄、結束放 C 欄，E2 的公式可以往下複製。B2、C2 會跟著列號移動，假日範圍 $J$2:$J$4 則固定不動；新增假日超過 J4 時，記得同步擴大這個範圍。
班段不同時，要一起改公式中的上午開始、上午結束、下午開始與下午結束四個 TIME。這篇的公式是照 09–12、13–18 的班段寫的；排班若還有補班、跨午夜或每天不同的時段，建議先整理逐日班表，再依那份班表重新設計公式。
只想知道兩個日期之間有幾個工作天，可以看 日期計算實戰 的 NETWORKDAYS；要換算時間或處理打卡差值，則看 時間函式與打卡工時。我是喜特先生，Mr. Sheet，我們下個教學見！
接下來可以
相關文章
查看所有文章時間函式整理：TIME、HOUR、MINUTE、SECOND、TIMEVALUE 與打卡工時
TIME 組時間，HOUR、MINUTE、SECOND 拆時間，TIMEVALUE 把文字時間轉成數字。附時間是一天的分數的換算表，以及打卡紀錄算工時、跨午夜、加總超過 24 小時的做法。
年資、工作日、到期日：日期計算實戰
用 DATEDIF 算員工年資，用 NETWORKDAYS 和 WORKDAY 搭配假日清單算工作日，再用 EDATE 和 EOMONTH 算合約到期日與月底結帳日。
日期與時間入門：日期序號、DATE、TODAY 與文字日期
從日期序號開始，認識 DATE、TODAY、NOW、YEAR、MONTH、DAY，學會日期加減、分辨文字日期和真日期，並搞清楚顯示格式和儲存格的值是兩回事。
留言討論
共 0 則留言