可重複使用的 Excel 工時表公式為 =MAX(0,MOD(ClockOut-ClockIn,1)*24-BreakMinutes/60),可傳回標準班次或跨夜班次的每日淨工時。

Excel 會將時間儲存為一天的比率,因此將兩個時間儲存格相減,所得為經過的天數,而非經過的小時數。公式會將該差值轉換回小時數,處理下班時間早於上班時間的情況,扣除無薪休息分鐘數,並避免每日結果出現負數。本指南的其餘內容會以此公式為基礎建立由星期一至星期日的工作表,接著再說明另一種以瀏覽器檢查每週總時數的方法。

formula to calculate timesheet in excel
在 Excel 中計算工時表的公式:可重複使用指南

每個工作日的公式結構

使用清楚的儲存格版面:B 欄為上班時間、C 欄為下班時間、D 欄為無薪休息分鐘數,E 欄為每日淨工時。將來源輸入值與計算值分開存放,可讓每一列更容易稽核,也能讓你將相同公式向下複製到整張工作表。

計算項目 公式模式 結果
每日淨工時 =MAX(0,MOD(C2-B2,1)*24-D2/60) 扣除無薪休息後的班次時數,並將負值調整為零
每週淨工時 =SUM(E2:E8) 由星期一至星期日的淨工時總數

MOD 函式會將時間差值包覆在一天內。乘以 24 可將一天的比率轉換為小時數。將休息時間除以 60 可將分鐘數轉換為小時數,而 MAX 會讓最終每日值維持為零或以上。除非已為開頭公式中的描述性名稱建立定義,否則請使用 B2、C2 等儲存格參照。

在 Excel 中建立可重複使用的每週工時表

  1. 建立工作表欄位。在第 1 列加入 Day、Clock In、Clock Out、Unpaid Break (min) 和 Net Hours。在第 2 列至第 8 列輸入星期一至星期日,並將第 9 列標示為 Weekly Total。
  2. 輸入時間值與休息時間。在 B 欄和 C 欄輸入實際時間值,例如 09:00 和 17:00,而不要在同一批儲存格中加入說明文字。在 D 欄以單純的分鐘數輸入無薪休息時間。未使用的日期保持空白;每日公式會對這些日期傳回零。
  3. 加入每日公式。在 E2 儲存格輸入 =MAX(0,MOD(C2-B2,1)*24-D2/60)。將 E2 向下複製至 E8,讓每一列都參照各自的上班、下班與休息儲存格。
  4. 計算每週總數。在 E9 輸入 =SUM(E2:E8)。將每日與總計儲存格格式化為具有兩位小數的數值,讓零數小時容易閱讀。
  5. 加入基本薪資估算。在 H2 輸入時薪,在 H3 輸入貨幣標示,並在 H4 輸入公式 =E9*$H$2。使用估算所需的貨幣,將 H4 格式化為貨幣格式。
  6. 輸入完整一週的資料前先測試。將一個一般班次和一個跨夜班次與每日總數進行核對。同時確認複製後的公式參照正確列,且 E9 中的總數未被覆蓋。

檢查一個跨夜班次

將 B2 設為 22:00、C2 設為 06:00,D2 設為 30。每日公式為 =MAX(0,MOD(C2-B2,1)*24-D2/60),代入後為 =MAX(0,(8/24)*24-30/60),並傳回 7.50 淨小時。這項檢查可確認班次持續至隔天早上,且無薪休息時間只扣除一次。

無須手動更正即可處理特殊情況

  • 跨夜班次:MOD 會將負的時間差值轉換為當天剩餘的時間。因此,下班時間早於上班時間時,會視為於隔天早上結束班次。
  • 開始與結束時間相同:時間差值為零,因此每日總數為零,而非完整的 24 小時。
  • 休息時間大於班次長度:減法可能產生負值,但 MAX 會為該日傳回零。負數不會帶入每週加總。
  • 非工作日留空:這些列可以保留在公式範圍內。空白輸入會產生零,不必刪除該列或輸入虛構時間,即可略過。
  • 以文字輸入的時間:使用文字格式的時間值可能造成算術錯誤。請重新輸入真正的 Excel 時間值,並對這些儲存格套用時間顯示格式。
  • 長於一天的班次:此時間模式假設班次於隔天早上結束,並非用來保留超過 24 小時班次中的額外完整天數。

加入每週薪資與自訂加班費估算

基本薪資為每週淨工時總數乘以時薪。當 E9 包含每週時數而 H2 包含費率時,=E9*$H$2 可直接產生估算值。如果工作表只需要追蹤時數,請讓費率儲存格保持空白。

若要使用使用者自訂的每週加班費計算,請在 H6 輸入每週時數門檻,在 H7 輸入加班費倍數。然後在 H8 使用 =MIN(E9,$H$6)*$H$2+MAX(0,E9-$H$6)*$H$2*$H$7。第一個部分會將基本費率套用至門檻以前的時數,第二個部分則將所選倍數套用於超過門檻的時數。

這些公式只會計算你編寫的規則,不能判定法定權益、每日加班費、薪資扣減或其他與僱傭相關的處理方式。將估算值用於薪資計算前,請依適用於你合約與所在地點的規則,確認門檻、倍數及四捨五入方法。

比較 Excel 工作表與瀏覽器計算工具

如果目標是進行一次性檢查,而非建立可重複使用的工作簿,Timesheet Calculator 提供另一套瀏覽器作業流程,可處理上班時間、下班時間、休息時間、每週總數及選擇性的薪資。若需要公式持續連結至可編輯的儲存格,它便無法取代工作表。

需求 Excel 工作表 Timesheet Calculator
資料輸入 可自訂時間、休息分鐘數及計算淨工時的欄位 由星期一至星期日的列,可輸入上班時間、下班時間與休息分鐘數
每日輸出 複製的公式,會在每個每日列傳回淨工時 在每個已完成日期旁顯示兩位小數的淨工時
跨夜工作 MOD 會將較早的下班時間轉換為隔天早上結束的班次 將較早的下班時間視為於隔天早上結束
休息時間超過班次長度 MAX 會將過大的休息時間調整值降為零 休息時間長於班次的日期會計算為零
每週總數 SUM 會加總每日淨工時儲存格 每日列下方會顯示累計每週總數
薪資與加班費 由你建立及設定公式與參照儲存格 可選擇時薪、貨幣符號、每週門檻與倍數
最適用的情況 含公式與自訂格式的可重複使用工作簿 快速以瀏覽器計算一週總數

使用 Timesheet Calculator 取得獨立的每週總數

  1. 輸入每個工作日。開啟 Timesheet Calculator,以標準 24 小時格式輸入上班與下班時間,並輸入無薪休息分鐘數。計算工具會提供由星期一至星期日每一天的列,任何空白日期都會直接略過。
  2. 查看每日與每週結果。每個已完成日期旁會顯示兩位小數的淨工時,而累計每週總數會顯示在表格下方。相同上班與下班時間會計算為零,較早的下班時間則會視為隔天早上。
  3. 加入選擇性的薪資資訊。輸入時薪並選擇貨幣符號,即可估算總薪資。結果會以千位分隔符與兩位小數顯示。如果只需要工作時數,請讓費率保持空白。
  4. 僅在需要時設定加班費。開啟加班費規則,然後選擇每週門檻與倍數。此規則預設為關閉;啟用後,門檻以前的時數採用基本費率,超過門檻的時數則採用所選倍數。
  5. 依需求調整班表。變更時間、休息時間、費率或加班費設定後,每日工時、每週總數與薪資估算會立即在瀏覽器中重新計算。

所有計算皆使用純 JavaScript 在瀏覽器本機執行。輸入的時間、休息時間、費率與薪資數字不會傳送至伺服器,也不會儲存或分享。加班費選項僅為方便計算的數學運算,而非法律或薪資建議,因為適用規則會因國家、州及合約而異。

進行最終一致性檢查

  • 將每個上班、下班與休息時間輸入項目與原始工作紀錄進行比對。
  • 確認每個時間儲存格都包含真正的時間值,每個休息時間儲存格都包含分鐘數,而非文字標示。
  • 填入其餘列之前,抽樣檢查一個日間班次與一個跨夜班次。
  • 確認每日公式已複製至每個工作列,且每週總數使用 SUM 計算預定範圍。
  • 檢查薪資公式是否指向正確的時薪儲存格,以及顯示的貨幣是否與估算值相符。
  • 當結果將用於薪酬紀錄時,請另外套用相關的加班費與薪資規則。

當公式必須維持可重複使用、可編輯且在工作簿中可見時,Excel 工作表是實用的選擇。瀏覽器計算工具則負責不同任務:快速產生獨立的每週總數,同時讓輸入的班表保留在瀏覽器本機。

若想進一步瞭解,請參閱 How to Find Trapezium Area: Formula and Steps。

若想進一步瞭解,請參閱 How to Find Weight on Other Planets: Formula and Steps。