Excelで勤務時間を集計すると、始業時刻と終業時刻の差を求めるだけでは正しい結果にならないことがあります。
休憩時間の控除、日付をまたぐ深夜勤務、月間合計が24時間を超えるケース、所定労働時間を超えた残業時間など、勤務表には確認したい条件がいくつもあります。
しかし、時刻をExcelの数値として扱う仕組みを理解し、用途に合う関数と表示形式を設定すれば、毎日の勤怠計算は自動化できます。
勤務時間の基本式は、終業時刻－始業時刻－休憩時間です。
日またぎ勤務では、終業時刻に1日分を加える考え方を使います。
月間合計では、表示形式を［h］:mmにすることが重要です。
この記事では、勤務時間、休憩、深夜、合計、残業時間をExcelで計算する実用的な方法を、1行目が見出しのサンプル勤務表を使って解説します。
入力する時刻は文字列ではなく、Excelが時刻として認識する値にすることが、正確な集計への第一歩です。
エクセルで勤務時間を計算する基本式
| 日付 | 始業 | 終業 | 休憩 | 勤務時間 |
|---|---|---|---|---|
| 4月1日 | 9:00 | 18:00 | 1:00 | 8:00 |
それではまず、始業時刻と終業時刻、休憩時間から実働時間を求める基本の計算について解説していきます。
時刻データとシリアル値の仕組み
Excelでは、1日を1という数値で管理しており、1時間は24分の1、1分は1440分の1として保存されています。
たとえば9:00と18:00を入力して差し引くと、Excel内部では9時間分の数値が計算され、セルの表示形式によって9:00と表示されます。
見た目が時刻でも、Excelにとっては計算可能な数値であるため、足し算や引き算、関数による条件判定ができます。
一方で、9時のような文字列や、全角の記号を含む入力は計算できない場合があります。
時刻を入力した後にセルを選択し、ホームタブの表示形式で時刻が選ばれているか確認しましょう。
時刻の入力例は9:00、18:30、0:45です。
休憩時間も1:00や0:45のように、時間と分の形式で入力します。
実働時間を求めるセル参照式
サンプルでは、A列を日付、B列を始業時刻、C列を終業時刻、D列を休憩時間、E列を勤務時間とします。
2行目のE2セルには、終業時刻から始業時刻と休憩時間を引く式を入力します。
=C2-B2-D2
この式では、18:00から9:00を引いて9時間とし、さらに休憩の1:00を引くため、結果は8:00になります。
計算式を入力した後は、E2セルの表示形式をユーザー定義のh:mmに設定すると、勤務時間を読みやすく表示できます。
休憩を引く計算は、始業と終業の差を出した後に行うため、休憩時間を別セルに分けておくと修正にも強くなります。
数式を下の行へコピーする方法
勤務表には複数日のデータを入力するため、E2セルの数式を毎回手入力する必要はありません。
E2セルを選択し、セル右下の小さな四角であるフィルハンドルを下方向へドラッグすると、行番号に合わせて数式がコピーされます。
たとえばE3には=C3-B3-D3、E4には=C4-B4-D4というように、自動で参照先が変わります。
途中に空白行がない表であれば、フィルハンドルをダブルクリックして連続データの末尾までコピーする方法も便利です。
数式がコピーされた後は、任意の行の始業時刻と終業時刻を変え、勤務時間が連動して変化するか確認しましょう。
【操作のポイント】勤務時間の列だけでなく、始業・終業・休憩の各列も時刻形式に統一すると、入力ミスと計算エラーを見つけやすくなります。
休憩時間を控除する関数と入力方法
| 始業 | 終業 | 休憩開始 | 休憩終了 | 実働 |
|---|---|---|---|---|
| 8:30 | 17:30 | 12:00 | 13:00 | 8:00 |
続いては、休憩時間を控除する入力方法と、休憩の開始・終了時刻から計算する方法を確認していきます。
休憩時間を直接入力する勤務表
休憩が毎日1時間など、固定されている勤務先では、休憩時間をD列に直接入力する方式が分かりやすい運用です。
D2に1:00を入力し、勤務時間のセルに=C2-B2-D2を入力すれば、休憩を差し引いた実働時間が求められます。
休憩時間が45分なら0:45、90分なら1:30と入力します。
60分を0:60とは入力しないことも重要です。
0:60は時刻として意図しない処理になる可能性があるため、1:00として入力しましょう。
固定休憩の実働時間は、=C2-B2-D2で計算します。
休憩開始と終了から差し引く計算
休憩時間が日によって変わる場合は、休憩開始と休憩終了を別々の列に入力すると、実績に基づく計算ができます。
たとえばD列を休憩開始、E列を休憩終了、F列を実働時間にした場合、F2には次の式を入力します。
=C2-B2-(E2-D2)
この数式は、終業から始業を引いた拘束時間から、休憩終了と休憩開始の差を引く仕組みです。
始業8:30、終業17:30、休憩開始12:00、休憩終了13:00なら、拘束9時間から休憩1時間を引いて8時間となります。
休憩が複数回ある場合は、それぞれの休憩時間を別列で求めて合計し、最後にまとめて控除する方法もあります。
未入力時に表示を空白にするIF関数
始業時刻や終業時刻が未入力の行に計算式をコピーすると、0:00や不自然な値が表示されることがあります。
入力前の行を空白にしたいときは、IF関数で始業または終業セルが空かどうかを判定します。
=IF(OR(B2=””,C2=””),””,C2-B2-D2)
この式はB2またはC2が空白なら空白を返し、両方に時刻が入っている場合だけ勤務時間を計算します。
空白セルを前提にしたIF関数を最初に設定すると、翌月以降に勤務表を複製して使う場合にも整った見た目を保てます。
【操作のポイント】休憩を直接入力する列と、休憩開始・終了を入力する列は混在させず、勤務先のルールに合う方式を一つ決めて運用します。
日またぎ勤務と深夜時間の計算
| 始業 | 終業 | 休憩 | 勤務時間 | 深夜時間 |
|---|---|---|---|---|
| 22:00 | 5:00 | 1:00 | 6:00 | 5:00 |
続いては、終業時刻が翌日になる夜勤と、22時から5時までの深夜時間を計算する考え方を確認していきます。
終業時刻が始業時刻より小さい場合
始業22:00、終業5:00のような勤務では、単純に終業から始業を引くと負の時間になり、Excelでは####と表示されることがあります。
この場合は、終業時刻が始業時刻より小さいときに1日分を足すIF関数を使います。
=IF(C2<B2,C2+1-B2-D2,C2-B2-D2)
終業時刻が始業時刻より前なら、C2に1を加えて翌日の時刻として扱います。
22:00から翌5:00までの拘束時間は7時間であり、休憩1時間を引くと実働は6時間です。
日またぎの判定は終業時刻と始業時刻の大小比較で行えるため、日付列を増やさない簡易勤務表にも利用できます。
深夜帯に重なる時間の考え方
深夜労働の集計では、一般に22:00から翌5:00までに勤務した時間を取り出します。
ただし、休憩が深夜帯に入るかどうか、割増賃金をどの単位で丸めるかなどは、就業規則や給与計算のルールを確認する必要があります。
始業と終業だけで深夜時間を求める簡易的な考え方では、勤務時間のうち22:00以降から翌5:00までの重なりを計算します。
夜勤の開始が22:00で終了が5:00なら、休憩を除く深夜時間は実働時間と同じ6時間になるとは限りません。
たとえば深夜帯の1:00から2:00に休憩した場合、深夜の実労働は6時間です。
休憩が20:00から21:00なら、深夜の実労働は7時間となります。
日付付き時刻で精度を高める方法
夜勤、交替制、複数日にまたがる勤務を正確に管理するなら、日付と時刻を一つのセルに入力する方法が確実です。
始業セルに2026/4/1 22:00、終業セルに2026/4/2 5:00のように入力すると、単純な引き算でも7時間を求められます。
実働時間は終業日時から始業日時と休憩時間を引く式で計算できます。
日付を含む値は表示形式をyyyy/m/d h:mmにすると、翌日扱いになっているか目視でも確認しやすくなります。
深夜勤務が多い職場ほど、日付付き時刻で記録する方法が入力確認と監査に向いています。
【操作のポイント】深夜時間を給与へ反映する勤務表では、休憩の時間帯と端数処理の規則も別途明文化しておくと、計算結果の説明がしやすくなります。
月間合計と二十四時間超の表示形式
| 日付 | 勤務時間 | 残業時間 |
|---|---|---|
| 4月1日 | 8:00 | 0:00 |
| 4月2日 | 7:30 | 0:00 |
| 合計 | 15:30 | 0:00 |
続いては、毎日の勤務時間を月単位で合計し、24時間を超えても正しく表示する設定を確認していきます。
SUM関数で勤務時間を合計する方法
日ごとの勤務時間がE2からE31に入力されている場合、月間合計を表示するセルにはSUM関数を入力します。
=SUM(E2:E31)
この式で、各行の勤務時間をまとめて加算できます。
ただし、合計が24時間を超えたときに通常のh:mm形式のままだと、時間が0に戻ったように見えることがあります。
たとえば32時間をh:mm形式で表示すると8:00となり、1日を超えた24時間分が表示から除かれてしまいます。
勤務時間の合計セルだけは表示形式を変える必要があることを覚えておきましょう。
ユーザー定義で［h］:mmを設定する手順
月間合計のセルを選択し、セルの書式設定を開いて表示形式のユーザー定義を選びます。
種類の入力欄に［h］:mmを設定すると、24時間を超えた分も累積時間として表示されます。
32時間なら32:00、168時間なら168:00と表示されるため、勤務実績の確認に適した形式です。
角かっこ付きのhは、経過時間を表すための指定です。
日ごとの勤務時間にはh:mm、月間合計には［h］:mmを使い分けると、勤務表の意味が明確になります。
合計セルが日付として表示される場合も、数式ではなく表示形式の設定を見直しましょう。
画面上で数式と合計セルを確認する手順
月間合計の確認では、数式バーにSUM関数が入っていることと、セルの表示形式が［h］:mmであることを順に見ます。
赤枠の合計セルを選択した状態で、セルの書式設定からユーザー定義を開き、［h］:mmを指定します。
勤務時間の総計が想定より少なく見えるときは、最初にこの表示形式を確認するのが近道です。
【操作のポイント】合計セルの数式をコピーする前に［h］:mmの表示形式を設定しておくと、集計値を確認するたびに誤解しません。
所定労働時間を超える残業時間の算出
| 勤務時間 | 所定時間 | 残業時間 |
|---|---|---|
| 9:30 | 8:00 | 1:30 |
続いては、実働時間から所定労働時間を差し引き、残業時間を自動で表示する計算式を確認していきます。
八時間を超えた時間を求めるMAX関数
実働時間がE2セル、所定労働時間を8時間とする場合、残業時間をF2セルで求めるにはMAX関数が便利です。
=MAX(0,E2-TIME(8,0,0))
TIME関数は、時間・分・秒から時刻の値を作る関数です。
この式では、実働時間から8時間を引いた結果と0を比べて、大きい方を表示します。
実働が9:30なら1:30、実働が7:45なら0:00となります。
MAX関数を使うと、所定時間に満たない勤務でマイナスの残業時間が表示されません。
所定時間を別セルに置く勤務表
部署、雇用形態、曜日によって所定労働時間が異なる場合は、固定値を数式に書かず、所定時間の列を用意します。
F列を所定時間、G列を残業時間にした場合、G2には次の式を入力します。
=MAX(0,E2-F2)
月曜日から金曜日は8:00、短縮勤務日は6:00などとF列に入力すれば、同じ数式を全行にコピーできます。
所定時間を一か所にまとめて管理したい場合は、別シートの設定セルを絶対参照する方法も有効です。
たとえば設定シートのB2に8:00があるなら、=MAX(0,E2-設定!$B$2)のように指定します。
変わる可能性がある基準値はセル参照にすることで、勤務ルールが更新された際の修正漏れを防げます。
法定外残業と勤怠ルールの確認
Excelの数式は時間差を正確に求められますが、残業の定義そのものは会社の就業規則や勤務形態によって異なります。
所定労働時間を超えた時間、1日8時間を超えた時間、週40時間を超えた時間は、必ずしも同じ集計項目ではありません。
フレックスタイム制、変形労働時間制、休日勤務、深夜勤務では、給与計算上の扱いが変わる場合があります。
そのため、この記事の数式は勤務表の集計補助として使い、賃金や割増の最終判断は自社の規程と担当部署の基準に合わせましょう。
勤務時間の集計式と給与計算の判定式を分けて考えることが、安全な運用につながります。
【操作のポイント】残業時間のセルも月間合計するなら、合計欄には［h］:mmを設定し、24時間を超えた残業を正しく表示します。
勤務時間のエラー表示と入力ミスの対処
| 表示 | 主な原因 | 確認箇所 |
|---|---|---|
| #### | 負の時間 | 日またぎ判定 |
| 0:00 | 入力が文字列 | 時刻形式 |
続いては、勤務時間が正しく表示されないときに確認したい代表的な原因と修正方法を確認していきます。
シャープ記号が表示される原因
勤務時間のセルに####が表示されるときは、列幅が足りない場合と、負の時間になっている場合があります。
まず列見出しの境界線をドラッグして列幅を広げ、それでも直らない場合は数式を確認します。
夜勤で終業時刻が始業時刻より早いのに、=C2-B2-D2のような通常式を使うと負の時間になります。
その場合は、日またぎ勤務用のIF関数に変更しましょう。
####は単なる表示幅の不足とは限らないため、勤務時刻の前後関係も確認が必要です。
時刻が文字列として入力された場合
セルの左寄せ表示、数式を入れても計算結果が変わらない、SUM関数で合計されないといった現象は、時刻が文字列になっている可能性を示します。
対象セルを選択し、数式バーを見て先頭にアポストロフィがないか、入力値に余分な空白がないかを確認します。
文字列の9:00を時刻に直すには、セルを選択してデータタブの区切り位置を利用する方法や、VALUE関数を使う方法があります。
簡単な方法としては、セルを編集状態にしてEnterキーを押し、表示形式を時刻に設定し直す方法があります。
外部システムから貼り付けた勤怠データでは、文字列化が起きやすいため注意しましょう。
端数処理と丸め関数の使い分け
勤務時間を15分単位や30分単位で集計したい場合は、MROUND関数、ROUNDUP関数、ROUNDDOWN関数などを使うことがあります。
たとえば実働時間E2を15分単位で切り捨てる場合、次の式が使えます。
=FLOOR(E2,TIME(0,15,0))
ただし、出退勤時刻や残業時間の丸め方には、法令や社内規則との整合性が必要です。
都合よく一方方向だけへ丸める設定は、運用上の問題になるかもしれません。
Excelでは計算できても、どの時点で何分単位に丸めるかは、会社で決められた規則に従いましょう。
【操作のポイント】エラーを見つけたら、数式を変更する前に始業・終業・休憩セルが時刻として認識されているかを確認します。
まとめ エクセルで勤務時間を計算する方法
Excelで勤務時間を計算する基本は、終業時刻から始業時刻と休憩時間を引く式です。
=C2-B2-D2のようなシンプルな数式でも、通常の日勤における実働時間を自動計算できます。
終業時刻が翌日になる勤務では、IF関数で1日分を加える日またぎ判定を設定します。
月間合計や残業時間の合計では、表示形式を［h］:mmに変更することが欠かせません。
所定労働時間を超えた残業はMAX関数で計算すると、負の値を出さずに管理できます。
休憩、深夜、端数処理、割増賃金の条件は職場ごとに異なるため、数式へ反映する前に勤怠規程を確認しましょう。
入力列と計算列を分け、時刻形式を統一した勤務表にすれば、日々の確認から月末の集計までを効率よく進められます。
