エクセルで勤務時間を集計すると、始業時刻と終業時刻を入力するだけで実働時間、休憩時間、残業時間を自動計算できる勤務表を作成できます。
手書きのタイムカードや電卓での計算では、分単位の計算ミス、日をまたぐ勤務の処理、月間合計の確認に時間がかかりがちです。
しかし、勤務時間計算テンプレートを用意しておけば、日々の入力作業を減らしながら、給与計算や勤怠管理に使いやすい一覧表へ整えられます。
無料で作れるエクセルのテンプレートでも、時刻の表示形式と数式を正しく設定すれば、実務で役立つ自動計算表になります。
勤務時間計算テンプレート作成の基本ポイント
・始業時刻と終業時刻は時刻として入力する
・実働時間は終業時刻から始業時刻と休憩時間を引く
・残業時間は所定労働時間を超えた分だけを計算する
・合計欄には角括弧付きの時間表示形式を設定する
この記事では、エクセルの勤務時間計算テンプレートを作成し、毎月の勤怠管理へ活用する方法を詳しく解説していきます。
エクセルの勤務時間計算テンプレート作成方法
| 日付 | 始業 | 終業 | 休憩 | 実働 | 残業 |
|---|---|---|---|---|---|
| 4月1日 | 9:00 | 18:00 | 1:00 | 8:00 | 0:00 |
| 4月2日 | 9:15 | 19:00 | 1:00 | 8:45 | 0:45 |
それではまず、勤務時間を自動計算する基本テンプレートについて解説していきます。
ここでは、1行目を見出し行、2行目以降を1日分の勤務記録として扱います。
A列に日付、B列に始業時刻、C列に終業時刻、D列に休憩時間、E列に実働時間、F列に残業時間を配置する構成です。
入力列と計算列の配置
勤務時間テンプレートは、入力するセルと数式を入れるセルを分けて作ることが基本です。
1行目には、A1セルへ日付、B1セルへ始業時刻、C1セルへ終業時刻、D1セルへ休憩時間、E1セルへ実働時間、F1セルへ残業時間と入力します。
2行目からは日ごとのデータを入力します。
たとえばA2セルに4月1日、B2セルに9:00、C2セルに18:00、D2セルに1:00を入力するイメージです。
始業、終業、休憩の各列を文字列ではなく時刻データとして入力することが、正確な自動計算の前提になります。
時刻を入力したセルでは、表示が9:00や18:00のようになっていれば問題ありません。
入力時に9時、18時などの文字を混ぜると、数式で引き算ができない場合があります。
テンプレートを複数人で共有する場合は、入力欄を淡い黄色、数式欄を淡い緑色に塗り分けると、修正すべきセルが分かりやすくなります。
【操作のポイント】勤務表では、入力欄と自動計算欄を色で分けると、数式を上書きするミスを防ぎやすくなります。
実働時間の基本数式
実働時間は、終業時刻から始業時刻を引き、さらに休憩時間を差し引いて求めます。
E2セルに入力する基本数式
=C2-B2-D2
この数式では、C2セルの終業時刻からB2セルの始業時刻を引き、その結果からD2セルの休憩時間を引きます。
始業が9:00、終業が18:00、休憩が1:00なら、E2セルには8:00と表示されます。
数式を入力した後は、E2セルを選択し、セル右下の小さな四角を下方向へドラッグしてオートフィルを実行しましょう。
これにより、E3セル以降にも行番号に応じた数式が自動でコピーされます。
コピー後の数式は、各行の始業時刻、終業時刻、休憩時間を参照する相対参照であることを確認してください。
実働時間が0:00や####と表示される場合は、時刻の入力値、数式、列幅、表示形式を順に確認すると原因を見つけやすくなります。
所定労働時間と残業時間の計算
一般的なフルタイム勤務では、休憩を除いた1日の所定労働時間を8時間として扱うことが多くあります。
残業時間は、実働時間が8時間を超えたときだけ、その超過分を表示する式にすると便利です。
F2セルに入力する残業時間の数式
=MAX(0,E2-TIME(8,0,0))
TIME関数のTIME(8,0,0)は8時間を表します。
MAX関数は複数の値のうち大きい値を返す関数です。
そのため、実働時間が8時間未満なら0:00を、8時間45分なら0:45を返します。
会社の就業規則で所定労働時間が7時間30分の場合は、TIME(8,0,0)をTIME(7,30,0)へ変更してください。
法定外残業、深夜勤務、休日出勤などを厳密に給与計算へ反映する場合は、会社の規定や労務担当者の運用に沿って別列を設ける必要があります。
【操作のポイント】所定労働時間は固定値ではなく、勤務先の規定に合う時間へ変更して使用しましょう。
勤務時間の表示形式と合計計算
| 項目 | 日別の例 | 月間合計 |
|---|---|---|
| 実働時間 | 8:00 | 160:30 |
| 残業時間 | 0:45 | 12:15 |
続いては、勤務時間を正しく表示し、月間合計を確認する設定を見ていきます。
時刻と時間の違い
エクセルでは、9:00という始業時刻も、8:00という実働時間も、内部では1日を1とする数値として管理されています。
たとえば12時間は0.5、6時間は0.25として扱われます。
この仕組みがあるため、時刻同士の引き算や休憩時間の差し引きが可能です。
一方で、始業時刻や終業時刻には時刻表示、実働時間や残業時間には経過時間表示を使い分けることが大切です。
日別の実働時間はh:mm表示で問題ありませんが、月間合計では角括弧付きの表示形式が必要になることがあります。
時間表示の意味を理解すると、勤務時間の合計が正しくないように見えるトラブルを防げます。
月間合計のSUM関数
月の勤務時間を合計するには、最終行の下に合計行を作り、SUM関数を使います。
たとえば2行目から32行目までの実働時間を合計する場合、E33セルへ数式を入力します。
月間実働時間の合計数式
=SUM(E2:E32)
残業時間も同様に、F33セルへ=SUM(F2:F32)と入力すれば合計できます。
月の日数が31日より少ない場合でも、空白の行はSUM関数で自動的に無視されます。
途中に休日や有給休暇の行があっても、実働時間欄を空白または0:00にすれば、合計時間への影響を調整できます。
毎月同じ形式を使うなら、日付だけを更新してテンプレートを複製すると、表の作り直しを減らせます。
【操作のポイント】合計セルには、日別データの範囲だけを指定し、見出し行や合計行自身を含めないようにします。
二十四時間を超える合計表示
1か月の実働時間は、通常は100時間を超えます。
ところが、合計セルの表示形式がh:mmのままだと、24時間を超えた部分が繰り返され、160時間が16:00のように表示されることがあります。
これは計算が間違っているのではなく、時刻として表示していることが原因です。
月間合計セルで設定する表示形式
[h]:mm
合計セルを右クリックし、セルの書式設定から表示形式を開き、ユーザー定義で[h]:mmを指定します。
角括弧を付けたhは、24時間を超えても時間数を累積表示する書式です。
160時間30分は160:30と表示されるため、勤怠表や給与計算用の確認が格段にしやすくなります。
残業時間、有給休暇の時間換算、休日労働時間の合計にも同じ表示形式を使えます。
【操作のポイント】24時間を超える可能性がある集計セルは、必ず[h]:mmの表示形式へ変更しましょう。
休憩時間と日またぎ勤務の自動計算
| 始業 | 終業 | 休憩 | 勤務区分 | 実働 |
|---|---|---|---|---|
| 22:00 | 6:00 | 1:00 | 夜勤 | 7:00 |
続いては、休憩時間の処理と、終業時刻が翌日になる夜勤の計算方法を確認していきます。
休憩時間を別セルで管理する方法
休憩時間を固定の1時間として扱う職場もあれば、勤務時間やシフトによって異なる職場もあります。
後から確認や修正をしやすくするため、休憩時間は数式に直接書かず、D列のような独立した列へ入力する方法が向いています。
たとえば休憩60分は1:00、休憩45分は0:45と入力します。
実働時間の数式は=C2-B2-D2のままで、D列の値だけを変更すれば計算結果が更新されます。
休憩を別セルにする設計は、早出や時短勤務、変則シフトがある勤務表ほど有効です。
勤務時間が6時間未満の日は休憩なし、6時間以上の日は45分、8時間以上の日は60分という運用なら、条件付きの数式で休憩時間を自動判定することもできます。
ただし、実際の休憩取得状況と会社の規定が異なる可能性もあるため、勤怠管理の正式な記録では確認できる入力欄を残すと安心です。
【操作のポイント】休憩時間を入力列として独立させると、勤務条件の変更にもテンプレートを使い回しやすくなります。
翌日にまたがる勤務の数式
22:00に始業し、翌朝6:00に終業する夜勤では、単純に終業時刻から始業時刻を引くと負の値になります。
このケースでは、MOD関数を使うと、日付をまたいだ勤務時間も正しく計算できます。
夜勤を含む実働時間の数式
=MOD(C2-B2,1)-D2
MOD(C2-B2,1)は、終業時刻から始業時刻を引いた結果を1日単位で調整する式です。
22:00から翌日6:00までなら8時間となり、休憩1時間を引くと実働時間は7:00です。
日勤だけの表でもこの式は使えるため、夜勤が混ざる可能性がある職場では最初から採用してもよいでしょう。
日またぎ勤務では、終業時刻だけでなく、勤務日の日付をどちらの日に計上するかという社内ルールも確認が必要です。
深夜帯の割増時間を管理したい場合は、深夜時間専用の列を追加し、22時から翌5時までの重複時間を別途計算する設計が実用的です。
空白行とエラー表示の防止
テンプレートでは、月末までの行へあらかじめ数式をコピーしておくことが多くあります。
このとき、始業時刻と終業時刻が未入力の行にも0:00が表示されると、表が見づらくなるかもしれません。
入力がないときは空白を返すIF関数を組み合わせる方法がおすすめです。
空白行を空欄にする実働時間の数式
=IF(OR(B2=””,C2=””),””,MOD(C2-B2,1)-D2)
この式では、B2セルまたはC2セルが空白なら、結果として空白文字を返します。
始業と終業が入力された場合だけ、MOD関数による勤務時間の計算を実行します。
休憩時間が空白の場合に0として扱いたい場合は、D2へ0:00を入力するか、数式にIFERRORなどを組み合わせて調整しましょう。
【操作のポイント】未入力日の表示を空白にすると、勤務実績がある日とない日を一覧で判別しやすくなります。
勤務表テンプレートの入力規則と見やすい書式
| 日付 | 始業 | 終業 | 休憩 | 実働 |
|---|---|---|---|---|
| 4月3日 | 9:00 | 17:30 | 0:45 | 7:45 |
続いては、入力ミスを減らし、誰が見ても使いやすい勤務表に整える方法を解説していきます。
時刻入力用の表示形式
始業時刻、終業時刻、休憩時間のセル範囲には、あらかじめ表示形式を設定しておくと入力が安定します。
B2からD32などの対象範囲を選択し、セルの書式設定から時刻の形式を選びます。
始業時刻と終業時刻にはh:mm、休憩時間にもh:mmを指定すると、9:00や0:45の形式で表示できます。
日付列にはm月d日、またはyyyy/m/dの表示形式を設定すると、月ごとの勤務表として確認しやすくなります。
セルに9.5のような小数を入力すると時刻とは別の数値になるため、時刻は9:30のようにコロンを使って入力します。
テンプレートの最初の月を作成した後、翌月分へコピーする際は、日付列だけを更新すれば基本構造をそのまま利用できます。
【操作のポイント】時刻列の表示形式を先に指定しておくと、入力者ごとの表記ゆれを抑えられます。
入力規則による勤務区分の選択
勤務区分の列を追加し、出勤、在宅、休暇、休日、夜勤などを選べるようにすると、月間の勤務状況を把握しやすくなります。
入力規則のリストを使えば、セルをクリックして候補から勤務区分を選択できます。
たとえばG1セルへ勤務区分と入力し、G2からG32を選択してデータタブのデータの入力規則を開きます。
入力値の種類でリストを選び、元の値へ出勤,在宅,有給休暇,欠勤,休日と入力すると、選択用のドロップダウンが作成されます。
勤務区分を統一すると、フィルター機能で有給休暇や休日出勤だけを後から確認できます。
入力規則の候補は、会社の勤務体系に合わせて変更してください。
数式セルの保護
勤務表を複数人が入力する場合、実働時間や残業時間の数式を誤って削除してしまうリスクがあります。
この対策として、入力欄だけを編集可能にし、計算欄を保護する設定が役立ちます。
まず、始業、終業、休憩などの入力セルを選択し、セルの書式設定の保護タブでロックのチェックを外します。
次に、校閲タブからシートの保護を選択します。
これにより、ロックされた実働時間列や合計列を保護しつつ、ロックを外した入力セルにはデータを入力できます。
シート保護は数式の誤操作を防ぐための機能であり、共有前のテンプレートでは特に有効です。
【操作のポイント】保護を設定する前に、入力するセルのロックを解除しないと、すべてのセルが編集できなくなります。
無料テンプレートの月次運用と集計
| 集計項目 | 数式例 | 用途 |
|---|---|---|
| 実働時間合計 | =SUM(E2:E32) | 月間労働時間の確認 |
| 残業時間合計 | =SUM(F2:F32) | 残業実績の確認 |
続いては、作成した無料テンプレートを毎月の勤怠管理で活用する方法を見ていきます。
月ごとのシート複製
勤務時間テンプレートが完成したら、月ごとにシートを複製して管理すると便利です。
シート見出しを右クリックし、移動またはコピーからコピーを作成する設定を選ぶと、数式や書式を保ったまま新しいシートを作れます。
シート名を4月、5月、6月のように変更し、日付列だけを新しい月へ合わせて更新します。
曜日も表示したい場合は、日付の隣に曜日列を追加し、TEXT関数を使用します。
曜日表示の数式例
=TEXT(A2,”aaa”)
この数式では、A2セルの日付を基に月、火、水のような曜日を表示できます。
月次シートを複製する運用なら、複雑な数式や書式を毎月作り直す必要がありません。
【操作のポイント】元になるテンプレート用シートを残しておくと、誤って月次シートを壊した場合にも復元しやすくなります。
フィルターと条件付き書式
勤務日が多い月は、フィルター機能を使うと確認作業を効率化できます。
見出し行を選択してデータタブからフィルターを設定すると、勤務区分、日付、残業時間などの条件で行を絞り込めます。
残業時間が発生した日を目立たせたい場合は、条件付き書式も役立ちます。
F2からF32を選択し、条件付き書式で0より大きいセルを赤系の塗りつぶしに設定すると、残業がある日を視覚的に確認できます。
色による強調は計算の代わりではありませんが、入力漏れや長時間勤務の確認を早める補助になります。
必要以上に多くの色を使うと表が読みにくくなるため、入力欄、計算欄、注意が必要な値の3種類程度に絞るとよいでしょう。
給与計算前の確認項目
勤務時間テンプレートは、給与計算の元データを整理するためにも使えます。
ただし、残業代や深夜割増の計算には、労働契約、就業規則、締め日、端数処理のルールが関係します。
テンプレートの集計値をそのまま給与額として扱うのではなく、勤怠の確認資料として活用する考え方が安全です。
月末には、始業時刻と終業時刻の入力漏れ、休憩時間の未入力、実働時間の異常値、残業時間の合計を確認しましょう。
合計時間だけでなく、各日の記録まで確認することで、勤怠データの信頼性を高められます。
【操作のポイント】締め日より前に入力漏れを確認する習慣を付けると、月末の修正作業を減らせます。
勤務時間計算テンプレートのトラブル対処
| 症状 | 主な原因 | 確認方法 |
|---|---|---|
| ####と表示される | 列幅不足または負の時刻 | 列幅と日またぎ数式 |
| 合計が16:00になる | 通常の時刻表示 | [h]:mmの設定 |
続いては、勤務時間の自動計算で起こりやすい表示や数式のトラブルを確認していきます。
マイナス時間と####表示
実働時間のセルに####と表示されるときは、まず列の幅が狭すぎないかを確認します。
列の境界をドラッグして幅を広げるだけで解決する場合があります。
それでも直らない場合は、終業時刻が始業時刻より早く、単純な引き算で負の時間になっている可能性があります。
夜勤なら、=C2-B2-D2ではなく、=MOD(C2-B2,1)-D2へ変更してください。
負の時間は標準的な時刻表示では扱いにくいため、日またぎ勤務用の数式が重要です。
また、終業時刻の入力ミスで6:00ではなく16:00にすべきところを間違えたケースもあるため、元データの確認も欠かせません。
合計時間が二十四時間で戻る現象
実働時間の合計が160時間のはずなのに16:00と表示される場合、SUM関数ではなく表示形式が原因です。
合計セルを選択し、セルの書式設定からユーザー定義を開きます。
種類の入力欄へ[h]:mmを設定すると、累積時間として160:00のように表示されます。
表示形式を変更しても数式そのものは変わりません。
月間合計、年間合計、残業時間合計など、24時間を超えるすべての集計セルへ適用しましょう。
数式コピーと参照ずれ
オートフィルで数式を下へコピーしたとき、参照先が意図どおり変わっているかを確認します。
E2セルに=C2-B2-D2を入力してE3セルへコピーすると、通常は=C3-B3-D3へ変わります。
一方、所定労働時間を別セルに置く場合は、コピーしても参照先が動かない絶対参照を使います。
H1セルに所定労働時間の8:00を入力した場合の残業時間数式
=MAX(0,E2-$H$1)
$H$1のようにドル記号を付けると、数式を下へコピーしてもH1セルを固定して参照できます。
勤務条件を変更する可能性があるなら、所定労働時間を独立した設定セルに置く設計が便利です。
【操作のポイント】コピー後に数式バーを確認し、行番号が正しく変化しているかを見ると参照ずれを早期に発見できます。
まとめ エクセルの勤務時間計算テンプレートを活用する方法
| 設定項目 | 基本内容 |
|---|---|
| 実働時間 | 終業時刻から始業時刻と休憩時間を引く |
| 残業時間 | 所定労働時間を超えた時間を集計する |
| 月間合計 | SUM関数と[h]:mm表示形式を使う |
エクセルの勤務時間計算テンプレートは、始業時刻、終業時刻、休憩時間を入力し、実働時間と残業時間を数式で算出する構成が基本です。
日勤だけなら=C2-B2-D2、夜勤を含む場合は=MOD(C2-B2,1)-D2を使うと、勤務時間を自動計算できます。
残業時間にはMAX関数を組み合わせ、所定労働時間を超えた分だけを表示すると分かりやすくなります。
月間合計ではSUM関数を使い、表示形式を[h]:mmに設定することが重要です。
入力欄と計算欄を分け、数式セルを保護し、月ごとにテンプレートを複製する運用にすると、無料のエクセルでも使いやすい勤怠管理表になります。
まずは基本の勤務表を一つ作成し、自分や職場の所定労働時間、休憩時間、勤務区分に合わせて少しずつ調整していきましょう。
