【Excel 時間相減】6種公式完整教學｜跨日、換算、工時計算

Excel 時間相減是用結束時間減去開始時間（=B1-A1）來計算時間差，搭配格式設定與 MOD 函數可處理跨日、單位換算等進階情境。 本文完整教學 6 種公式應用，涵蓋同日相減、小時分鐘換算、跨日夜班、工時加總與午休扣除，附常見錯誤排查。

本文假設你已具備基本的 Excel 操作能力。如果需要複習時間計算的基礎觀念，可以搭配該篇一起閱讀。

開始前必知：Excel 時間的本質與格式設定

很多人在 Excel 輸入時間後直接相減，卻得到一串看不懂的小數或錯誤訊息。問題幾乎都出在「不了解 Excel 怎麼儲存時間」。這一節幫你打好基礎，後面的公式才不會卡關。

Excel 時間是「天的小數」：0.5 = 12:00

Excel 內部用一個介於 0 到 1 之間的小數來表示一天中的時間。0 代表 00:00（午夜），1 代表隔天的 00:00，所以 0.5 就是正午 12:00。

以下是常見時間與小數值的對照：

理解這個原理後，你就知道為什麼「時間相減的結果是小數」——因為 Excel 本來就是在做小數減法。

輸入前先設定儲存格格式

時間輸入最常見的錯誤是：儲存格格式被設為「文字」，導致輸入的 09:00 變成一串文字而非時間數值。

正確的操作順序：

1. 選取要輸入時間的儲存格範圍
2. 按右鍵 →「設定儲存格格式」→ 選擇「時間」類別
3. 選擇你需要的時間格式（如 13:30 或 1:30 PM）
4. 確認後再開始輸入時間

如果你已經輸入了時間卻發現格式不對，可以觀察儲存格的對齊方式：時間數值會靠右對齊，文字則靠左。靠左的話，代表 Excel 把它當文字處理了，需要重新設定格式並重新輸入。

12 小時制 vs 24 小時制輸入注意事項

Excel 預設接受 24 小時制輸入（如 17:30）。如果你習慣用 12 小時制，必須在數字後面加上空格和 AM 或 PM：

• ✅ 5:30 PM（注意 PM 前有空格）
• ✅ 09:00 AM（上午時間加 AM）
• ❌ 5:30PM（沒空格，Excel 可能無法辨識）
• ❌ 5:30 下午（中文不被接受）

建議統一使用 24 小時制輸入（如直接輸入 17:30 而非 5:30 PM），避免 AM/PM 混用造成的計算錯誤。

Excel 時間相減基本公式（同日情境）

這是最核心的操作——同一天內，用結束時間減去開始時間。

直接相減公式 =B1-A1

假設 A1 是上班時間 09:00，B1 是下班時間 17:30，在 C1 輸入：

=B1-A1

Excel 會計算出 0.354167 這個小數值（代表 8 小時 30 分鐘佔一天的比例）。但你看到的可能是 8:30 或一串數字，取決於 C1 的格式設定。

操作步驟：

1. 在 A1 輸入 09:00，B1 輸入 17:30
2. 在 C1 輸入公式 =B1-A1
3. 如果結果顯示為小數，右鍵 C1 →「設定儲存格格式」→ 選擇「自訂」→ 輸入 h:mm
4. 結果顯示為 8:30

結果格式化：h:mm 與 [h]:mm 的差異

這是很多人忽略的關鍵差異：

舉例：如果時間差是 26 小時 30 分鐘：

• h:mm 格式會顯示 2:30（只取 24 小時的餘數）
• [h]:mm 格式會顯示 26:30（完整的小時數）

當你在做週工時或月工時加總時，務必使用 [h]:mm 格式，否則超過 24 小時的部分會被吃掉。

常見錯誤即時排查

結果顯示 #VALUE!：最常見的原因是其中一個儲存格的內容是文字而非時間。檢查方式：選取該儲存格，看資料編輯列是否顯示正確的時間格式。如果是文字，需要重新設定格式並重新輸入。

結果顯示小數（如 0.354）：這不是錯誤，只是格式問題。將結果儲存格的格式改為 h:mm 即可。

結果為負數或顯示 ########：代表結束時間早於開始時間。如果是同日情境，請檢查輸入是否正確；如果是跨日（如夜班），請參考本文後面的跨日處理章節。

:mm]

將時間差換算為指定單位（小時、分鐘、秒）

計算出時間差之後，實務上你通常需要把結果轉換成特定單位——薪資計算要小數小時、通話記錄要分鐘數、運動計時要秒數。這一節教你所有時間換算的公式。

換算為小數小時（×24）：薪資計算最常用

這是人資和會計最常用的換算。將時間差乘以 24，就能得到小數小時：

=(B1-A1)*24

範例：09:00 到 17:30 的時間差 × 24 = 8.5（小時）

這個 8.5 可以直接乘以時薪來計算薪資。例如時薪 NT$200：

=(B1-A1)*24*200

結果為 NT$1,700。記得將結果儲存格格式設為「數值」而非「時間」，否則會顯示錯誤的時間格式。

換算為純分鐘數（×1440）與純秒數（×86400）

原理相同，只是乘數不同：

這些公式在計算通話時長（分鐘計費）或製程時間（秒級精度）時特別實用。

HOUR / MINUTE / SECOND 函數：分解顯示時分秒

如果你需要分別取出小時、分鐘、秒的數值，可以用這三個函數：

=HOUR(B1-A1)     → 取小時部分
=MINUTE(B1-A1)   → 取分鐘部分
=SECOND(B1-A1)   → 取秒部分

重要限制：HOUR 函數只能回傳 0-23 的值。如果時間差超過 24 小時（例如 26 小時），HOUR 會回傳 2 而非 26。

超過 24 小時的替代方案：

=INT((B1-A1)*24)                    → 總小時數（整數部分）
=INT(MOD((B1-A1)*1440,60))         → 剩餘分鐘數

組合顯示範例：=INT((B1-A1)*24)&"小時"&MINUTE(B1-A1)&"分鐘" → 顯示「26小時30分鐘」

TEXT 函數自訂顯示格式

TEXT 函數讓你一次搞定格式化，不需要額外設定儲存格格式：

=TEXT(B1-A1,"[h]:mm:ss")

常用格式字串對照表：

注意：TEXT 函數的結果是文字，無法再用於數學計算。如果你需要同時顯示格式化結果又要做後續計算，建議用一欄放數值公式、另一欄放 TEXT 格式化。

如果你經常需要處理日期相減的計算，可以搭配本文的時間公式一起使用，處理更複雜的日期時間差。

跨日與夜班時間相減

這是 Excel 時間相減最容易出錯的情境。當結束時間「看起來」比開始時間小（例如夜班 22:00 到隔天 06:00），直接相減會得到負數。這一節教你三種解法。

為何直接相減會出錯

回到 Excel 時間的本質：22:00 的內部值是 0.9167，06:00 的內部值是 0.25。

=06:00 - 22:00 → 0.25 - 0.9167 = -0.6667

結果是負數，Excel 預設無法顯示負數時間，所以你會看到 ######## 或格式錯誤。

MOD 函數解法：=MOD(B1-A1,1)

MOD 是處理跨日時間差最簡潔的公式：

=MOD(B1-A1,1)

原理：MOD(數值, 1) 會將結果限制在 0 到 1 之間。當 B1-A1 得到 -0.6667 時，MOD 會自動加上 1，變成 0.3333（即 8 小時）。

實際範例：

MOD 公式的好處是：不管是日班還是夜班，同一個公式都能正確計算。所以如果你的排班表混合日班和夜班，直接統一用 MOD 公式即可。

含日期的跨日計算

如果你的儲存格同時包含日期和時間（例如 4/1 22:00 和 4/2 06:00），Excel 能自動處理跨日問題，直接相減就好：

=B1-A1

因為 Excel 的日期時間是「天數 + 小數時間」的組合。4/1 22:00 的內部值大約是 45383.9167，4/2 06:00 是 45384.25，相減得到 0.3333（8 小時），完全正確。

建議：如果你的工作場景經常遇到跨日情境（如醫院排班、工廠輪班），在資料表中同時記錄日期和時間是最穩妥的做法，可以避免所有跨日計算問題。

負數時間差的處理：IF + MOD 組合

有時候你不確定哪個時間在前、哪個在後（例如使用者可能填反），可以用 IF 判斷後自動修正：

=IF(B1>=A1, B1-A1, MOD(B1-A1,1))

這個公式的邏輯是：

• 如果 B1 ≥ A1（正常情況），直接相減
• 如果 B1 < A1（跨日或填反），用 MOD 處理

更簡潔的寫法是直接用 MOD，因為它在兩種情境下都能正確運作。但如果你需要區分「跨日」和「填錯」的情況，IF 判斷會更有彈性。另一個常見做法是用 =ABS(B1-A1) 取絕對值，但此法無法區分跨日與填錯的情況，使用時需注意。

工時實務計算：排除午休、假日與加班統計

學會基本公式後，接下來是台灣職場最常遇到的實務情境。以一家 50 人製造業公司的排班管理為例，看看這些公式怎麼組合使用。

扣除午休的每日工時

台灣多數公司的午休時間是 12:00-13:00。如果員工 08:30 上班、17:30 下班，實際工時不是 9 小時，而是扣掉午休的 8 小時。

分段計算公式（假設 A1=上班、B1=下班、午休固定 12:00-13:00）：

=(12:00-A1)+(B1-13:00)

更彈性的寫法（午休時間放在獨立儲存格 D1=12:00、E1=13:00）：

=(D1-A1)+(B1-E1)

如果要換算成小數小時用於薪資計算：

=((D1-A1)+(B1-E1))*24

以 08:30 上班、17:30 下班為例：(12:00-08:30)+(17:30-13:00) = 3.5+4.5 = 8 小時。

加班時數計算

標準工時 8 小時，超過的部分就是加班。假設 A1=上班時間、B1=實際下班時間：

=MAX((B1-A1-1/24*9)*24, 0)

這裡 1/24*9 代表 9 小時（8 小時工時 + 1 小時午休）。MAX 函數確保沒有加班時結果為 0 而非負數。

更直觀的寫法：

=MAX((B1-A1)*24 - 9, 0)

另一種初學者容易理解的寫法，直接用標準工時的時間表示：

=B1-A1-"8:00"/24

這裡 8/24 代表 8 小時在 Excel 中的小數值。例如 08:30 上班、20:30 下班：=20:30-08:30-8/24 先算出扣除標準工時後的差值，再搭配 MAX 或 IF 判斷是否有加班。

範例：08:30 上班、20:30 下班 → (20:30-08:30) = 12 小時 → 12-9 = 3 小時加班

加班費計算（假設時薪 NT$200，加班前 2 小時 1.34 倍、之後 1.67 倍）：

=IF(加班時數<=2, 加班時數*200*1.34, 2*200*1.34+(加班時數-2)*200*1.67)

彈性工時或排班制的加班判斷更複雜，可結合 IF、SUMPRODUCT 等函數進行條件式計算，依據不同班別套用不同的標準工時門檻。

NETWORKDAYS 排除週末與國定假日

計算一段期間的工作天數，NETWORKDAYS 函數會自動排除週六日：

=NETWORKDAYS(開始日期, 結束日期, 假日範圍)

台灣國定假日的設定方式： 1. 在工作表的某個區域（如 G1:G20）列出當年度所有國定假日日期 2. 在公式中引用這個範圍：=NETWORKDAYS(A1, B1, G1:G20)

結合每日工時計算月總工時：

=NETWORKDAYS(月初日期, 月末日期, 假日範圍) * 8

這對人資部門計算應出勤時數特別實用。若需統計多員工多天工時，可結合 NETWORKDAYS 與 SUMPRODUCT 函數進行批次計算，一次算出整個部門的應出勤總時數。

如果你的排班表資料量很大，建議善用凍結窗格功能，讓標題列固定在畫面上方，方便對照欄位。

多筆工時加總（SUM + [h]:mm 格式）

把一週或一個月的每日工時加總，直接用 SUM：

=SUM(C1:C7)

關鍵步驟：加總結果的儲存格格式必須設為 [h]:mm（有方括號的 h）。如果用一般的 h:mm，超過 24 小時的部分會被截斷。

範例：一週五天每天 8:30 工時，SUM 結果：

• h:mm 格式顯示 → 18:30（錯誤！只顯示 42.5 小時除以 24 的餘數）
• [h]:mm 格式顯示 → 42:30（正確！）

如果需要將加總結果換算為小數小時：

=SUM(C1:C7)*24

結果為 42.5，可直接用於薪資計算。

:mm）、乘以24換算小數小時計算薪資]

在處理大量排班資料時，你可能還需要凍結多列來同時固定標題列和員工姓名欄，讓瀏覽更方便。

大量工時數據的自動化處理

當你的團隊超過 20 人，或需要跨部門彙整工時數據時，Excel 的手動維護會開始出現瓶頸：公式被誤改、版本衝突、沒人知道哪份檔案是最新的。

這時候可以考慮用專案管理工具來取代 Excel 的工時追蹤功能。以 monday.com 為例，它內建的時間追蹤欄位可以讓員工直接在任務卡片上按「開始/停止」計時，系統自動計算工時並生成報表——不需要任何公式。

具體來說，monday.com 解決了三個 Excel 做不到的事：

1. 即時協作：50 個人同時填寫工時，不會有檔案鎖定或版本衝突的問題
2. 自動化提醒：設定規則「如果某員工當天未填工時，下班時自動發送提醒」，可以顯著提升工時填寫的完成率
3. 一鍵匯出：工時數據可以直接匯出為 Excel 格式，方便會計部門做薪資計算

如果你目前的工時管理還在 Excel 階段但運作順暢，不需要急著換工具。但當工時整理成為固定的行政負擔時，就是該考慮升級的時候了。免費方案不需要信用卡，可以先用小團隊試跑。

結論

Excel 時間相減的核心觀念只有一個：時間在 Excel 裡就是小數，所有計算都是小數的加減乘除。

本文涵蓋的 6 種公式：

• 同日時間差：=B1-A1，結果格式設為 h:mm
• 跨日夜班：=MOD(B1-A1,1)，一個公式通吃日班夜班
• 換算小數小時：=(B1-A1)*24，薪資計算必備
• 換算分鐘/秒：乘以 1440（分鐘）或 86400（秒）
• 扣除午休工時：=(12:00-A1)+(B1-13:00)，分段計算淨工時
• 多筆加總：用 SUM 加總後，格式務必設為 [h]:mm

下一步行動：打開你的 Excel 排班表或工時表，把原本的手動計算替換成本文的公式。如果你管理的團隊超過 20 人，建議試試 monday.com 的工時追蹤功能，省下每月整理表格的時間。

想系統性提升 Excel 技能，可以參考 Coursera 的 Excel 課程，從基礎到進階函數都有完整教學，適合想考取認證或強化職場競爭力的讀者。

Excel 時間相減常見問題 FAQ

Excel 時間相減結果顯示 ########，怎麼辦？

這通常有兩個原因：一是欄位寬度不夠，拉寬欄位即可；二是時間差為負數（結束時間早於開始時間），Excel 無法以時間格式顯示負值。如果是跨日情境，改用 =MOD(B1-A1,1) 公式即可解決。

如何計算超過 24 小時的時間差？

將結果儲存格的格式設為 [h]:mm（h 外面加方括號）。一般的 h:mm 格式只會顯示 0-23 小時的餘數，例如 26 小時會錯誤顯示為 2 小時。設定方式：右鍵 →「設定儲存格格式」→「自訂」→ 輸入 [h]:mm。

時間輸入後變成數字，不是時間格式，如何修正？

先檢查儲存格格式是否為「文字」——文字格式的內容會靠左對齊。修正步驟：將格式改為「時間」後，需要重新輸入時間值（僅改格式不會自動轉換已輸入的文字）。如果資料量大，可以用「資料」→「資料剖析」功能批次轉換。

跨日夜班（如 23:00 到隔天 07:00）怎麼計算？

使用 MOD 函數：=MOD(B1-A1,1)。這個公式會自動將負數結果轉為正確的跨日時間差。以 23:00 到 07:00 為例，結果為 0.3333，格式化後顯示 8:00（8 小時）。如果儲存格包含完整日期時間（如 4/1 23:00 和 4/2 07:00），則直接 =B1-A1 即可。

如何將時間差換算成可以乘以時薪的小數？

將時間差乘以 24：=(B1-A1)*24。例如 8 小時 30 分鐘會變成 8.5，可以直接乘以時薪計算薪資。記得將結果儲存格格式設為「數值」而非「時間」，否則 8.5 會被顯示為錯誤的時間格式。完整的Excel 時間計算公式整理可參考我們的支柱頁面。