Excelで勤務時間を集計するときは、出勤時刻と退勤時刻だけを引き算するのではなく、休憩時間、日をまたぐ勤務、残業時間、合計時間の表示形式まで確認することが大切です。
とくに勤務表やタイムカードの集計では、通常の時刻表示のまま合計すると、24時間を超えた値が正しく見えないことがあります。
勤務時間の基本式は、退勤時刻－出勤時刻－休憩時間です。
日をまたぐ勤務は、MOD関数または翌日扱いの時刻計算で対応します。
月間合計を表示するセルには、[h]:mm形式の表示形式を設定します。
この記事では、1行目に見出しがある勤務表を例に、勤務時間と残業時間を正確に計算して合計する方法を解説します。
エクセルで勤務時間を計算する基本式
それではまず、出勤時刻、退勤時刻、休憩時間から実働時間を求める基本の方法について解説していきます。
| 日付 | 出勤 | 退勤 | 休憩 | 実働時間 |
|---|---|---|---|---|
| 4月1日 | 9:00 | 18:00 | 1:00 | 8:00 |
| 4月2日 | 8:30 | 17:45 | 0:45 | 8:30 |
この例では、A列に日付、B列に出勤時刻、C列に退勤時刻、D列に休憩時間、E列に実働時間を入力する構成です。
時刻データの入力方法
勤務時間の計算を始める前に、時刻をExcelが認識できる形式で入力する必要があります。
出勤時刻と退勤時刻は、9:00、18:00、8:30のように、時と分をコロンで区切って入力します。
時刻を文字列として入力すると引き算ができないため、セル内で右寄せ表示になっているかを確認しましょう。
セルが左寄せで表示される場合は、先頭にアポストロフィが付いていたり、文字列形式になっていたりする可能性があります。
休憩時間も、1時間なら1:00、45分なら0:45という時刻形式で入力します。
休憩を分単位で別管理している場合でも、計算用の列では0:30や1:15のような時間表記にそろえると扱いやすくなります。
空欄の日は無理に0:00を入れず、勤務が確定してから時刻を入力する運用でも問題ありません。
入力例として、B2に9:00、C2に18:00、D2に1:00を入れます。
これらの値は数値として保存され、24時間を1とする小数でExcel内部に記録されます。
実働時間の数式
続いては、実働時間を計算する数式を確認していきます。
E2セルには、退勤時刻から出勤時刻と休憩時間を引く式を入力します。
=C2-B2-D2
この式では、C2の退勤時刻からB2の出勤時刻を引き、さらにD2の休憩時間を差し引きます。
たとえば9:00から18:00まで勤務し、休憩が1:00なら、計算結果は8:00です。
休憩時間を引かないと拘束時間が実働時間として集計されるため、給与計算や残業判定の前に必ず確認が必要です。
E2に式を入れた後、セル右下のフィルハンドルを下へドラッグすると、各行に数式をコピーできます。
勤務日ごとに休憩時間が異なる場合でも、行ごとのD列を参照するため、自動的に実際の休憩時間を反映できます。
勤務時間セルの表示形式
続いては、実働時間の見え方を整える表示形式を確認していきます。
E列を選択し、ホームタブの表示形式から時刻を選ぶと、計算結果を時間と分で表示できます。
ただし、勤務時間の列では午前や午後が表示される形式ではなく、13:30や8:00のように時間数が見える形式が向いています。
セルの書式設定でユーザー定義を開き、h:mmを指定すると、日ごとの実働時間を読み取りやすくできます。
8時間未満の短時間勤務や、10時間を超える勤務も同じ列で自然に表示されます。
数式を入力しているのに#####と表示される場合は、列幅が不足していることが多いため、列の境界線を広げてください。
【操作のポイント】実働時間の列は、計算式を作成した直後にh:mm形式へ設定しておくと、入力ミスや休憩控除漏れを早く見つけやすくなります。
休憩時間を差し引く計算方法
続いては、固定休憩と勤務ごとに異なる休憩時間を正しく差し引く方法を確認していきます。
| 出勤 | 退勤 | 休憩開始 | 休憩終了 | 実働時間 |
|---|---|---|---|---|
| 9:00 | 18:00 | 12:00 | 13:00 | 8:00 |
| 10:00 | 19:30 | 14:00 | 14:45 | 8:45 |
休憩を開始時刻と終了時刻で記録する場合は、休憩時間そのものを別途入力しなくても算出できます。
固定休憩時間を引く数式
それではまず、毎日同じ休憩時間を取る職場で使いやすい数式について解説していきます。
たとえば休憩が毎日1時間で固定なら、E2セルに次の式を入力します。
=C2-B2-TIME(1,0,0)
TIME関数は、時、分、秒を指定して時刻データを作る関数です。
TIME(1,0,0)は1時間を意味するため、退勤時刻から出勤時刻と1時間を引く計算になります。
固定休憩の式は入力が簡単ですが、短時間勤務の日にも自動で1時間引かれる点に注意が必要です。
半日勤務や休憩なしの勤務が混在する場合は、休憩時間を個別列で管理する方法が安全でしょう。
固定休憩を使う場合も、就業規則に応じて45分ならTIME(0,45,0)へ変更できます。
休憩開始と終了から求める方法
続いては、休憩の開始時刻と終了時刻を記録する勤務表の計算方法を確認していきます。
たとえばD列を休憩開始、E列を休憩終了、F列を実働時間とした場合、F2には次の式を入力します。
=C2-B2-(E2-D2)
E2-D2で休憩時間を求め、その値を勤務の拘束時間から差し引く仕組みです。
12:00から13:00まで休憩した場合、E2-D2の結果は1:00になります。
外出、分割休憩、育児時間など、休憩が複数回ある勤務では、休憩ごとに列を分けて差し引く方法もあります。
ただし列が増えすぎると確認しにくくなるため、通常休憩と追加休憩を分ける程度にすると管理しやすいでしょう。
休憩終了時刻を入力し忘れると、実働時間が異常値になったり、エラーになったりします。
空欄時に結果を表示しない式
続いては、未入力行に0:00やエラーを表示させない方法を確認していきます。
勤務表をあらかじめ1か月分作っておくと、まだ勤務していない日の行にも数式が入ります。
このとき、出勤時刻が空欄なら結果も空欄にする式が便利です。
=IF(OR(B2=””,C2=””),””,C2-B2-D2)
OR関数は、B2またはC2のどちらかが空欄なら真になる条件です。
条件が真なら空欄を返し、両方が入力済みなら勤務時間を計算します。
未入力の予定行と勤務済みの行を見分けやすくしたいときは、空欄を返す式が役立ちます。
休憩時間が未入力でも計算したい場合は、D2を0として扱うIFERROR関数などを組み合わせることもできます。
【操作のポイント】休憩時間の扱いは、固定控除か実績控除かを最初に決め、勤務表の全行で同じルールを使うことが集計ミスの防止につながります。
日をまたぐ勤務時間の計算
続いては、夜勤や深夜勤務のように退勤時刻が翌日になるケースの計算方法を確認していきます。
| 出勤 | 退勤 | 休憩 | 実働時間 |
|---|---|---|---|
| 22:00 | 7:00 | 1:00 | 8:00 |
| 20:30 | 5:30 | 0:45 | 8:15 |
同じ日の時刻だけを引き算すると、22:00から7:00の勤務は負の値になってしまいます。
MOD関数を使う数式
それではまず、日をまたぐ勤務を簡潔に計算できるMOD関数について解説していきます。
E2セルに実働時間を表示する場合は、次の式を使います。
=MOD(C2-B2,1)-D2
MOD関数は、割り算の余りを返す関数です。
時刻は1日を1として扱うため、MOD(C2-B2,1)とすると、負の時間差を翌日に繰り越した時間として計算できます。
22:00から7:00までの差は、本来はマイナス15時間ですが、この式では9:00として扱われます。
そこから休憩1:00を引くため、実働時間は8:00になります。
夜勤の勤務表では、通常の引き算ではなくMOD関数を使うと、日付列を増やさずに計算できる点が便利です。
日中勤務にも同じ式を使えるため、日勤と夜勤が混在するシフト表にも適しています。
Excel画面での数式入力
続いては、MOD関数を入力し、オートフィルで各行へ反映する操作を確認していきます。
数式バーに=MOD(C2-B2,1)-D2と入力し、Enterキーを押すとE2に実働時間が表示されます。
赤枠で示したE2セルを選択し、右下に表示される小さな四角を下方向へドラッグします。
先頭行だけに正しい数式を入れ、オートフィルでコピーすれば、セル参照が各行に合わせて自動変更されます。
勤務人数が多い表では、数式を貼り付けるより、テーブル化して計算列を使う方法も便利です。
深夜勤務で確認したい注意点
続いては、深夜勤務の集計で見落としやすい注意点を確認していきます。
勤務時間の計算と、深夜割増の対象時間を計算する処理は別に考える必要があります。
実働時間が8:00であっても、そのうち何時間が深夜帯に該当するかは、勤務開始時刻と終了時刻から別途判定します。
また、退勤時刻だけでなく日付も入力している詳細な勤怠表では、終了日時から開始日時を引く方法がより確実です。
たとえば開始日時が2026年4月1日22:00、終了日時が2026年4月2日7:00なら、日付を含むセル同士の引き算で9時間を求められます。
夜勤が頻繁にある場合は、日付列と時刻列を組み合わせた開始日時、終了日時の列を作ると、勤怠の監査や確認がしやすくなります。
【操作のポイント】日またぎ勤務はMOD関数で実働時間を計算できますが、深夜割増や日付別の勤務実績まで扱うなら、日付を含む日時データで管理する方法が安定します。
残業時間を計算する数式
続いては、所定労働時間を超えた分を残業時間として表示する方法を確認していきます。
| 実働時間 | 所定労働時間 | 残業時間 |
|---|---|---|
| 8:00 | 8:00 | 0:00 |
| 9:30 | 8:00 | 1:30 |
残業時間は、実働時間が所定労働時間を上回った部分だけを表示するのが基本です。
8時間を超えた時間を求める式
それではまず、1日8時間を基準に残業時間を求める数式について解説していきます。
実働時間がE2にある場合、F2に次の式を入力します。
=MAX(0,E2-TIME(8,0,0))
MAX関数は複数の値のうち大きい値を返す関数です。
実働時間が8時間未満なら、E2-TIME(8,0,0)は負の値になりますが、MAX関数によって0:00が表示されます。
実働時間が9:30なら、8:00を引いた1:30が残業時間として表示されます。
単純に実働時間から8時間を引くと短時間勤務でマイナス表示になるため、MAX関数を組み合わせることが重要です。
所定労働時間が7時間30分の会社では、TIME(7,30,0)に置き換えます。
所定労働時間を別セルで管理する方法
続いては、部署や雇用形態ごとに基準時間が異なる場合の計算方法を確認していきます。
たとえばH1セルに所定労働時間として8:00を入力し、F2に次の式を設定します。
=MAX(0,E2-$H$1)
$H$1のようにドル記号を付けると、数式を下へコピーしても参照先がH1に固定されます。
この参照方法を絶対参照といいます。
月によって所定時間が変わる場合や、勤務区分ごとに基準を変える場合にも、設定セルを変更するだけで一括反映できます。
基準時間を数式内に直接書くより、専用セルで管理すると、就業ルールが変わったときの修正漏れを防ぎやすくなります。
基準時間のセルには、入力規則や色分けを設定して、誤って上書きしないようにする工夫も有効です。
残業時間の表示形式
続いては、残業時間を見やすく表示する書式を確認していきます。
日ごとの残業時間はh:mm形式で表示できますが、月間合計では24時間を超えることが一般的です。
このため、月合計のセルは[h]:mm形式に設定します。
h:mmのまま30時間を表示すると6:00のように見えることがありますが、[h]:mmなら30:00と正しく表示されます。
勤務時間や残業時間を月単位で合計するセルは、必ず角かっこ付きの[h]:mm表示にすることが重要です。
給与システムへ転記する際に小数時間が必要なら、別列で時間数へ変換する式を作成します。
【操作のポイント】残業時間は実働時間と所定労働時間を分けて管理し、日別の残業と月間の残業を同じ表示形式のまま混同しないようにします。
勤務時間と残業時間の合計
続いては、日ごとの実働時間と残業時間を月単位で合計する方法を確認していきます。
| 項目 | 4月1日 | 4月2日 | 4月3日 | 合計 |
|---|---|---|---|---|
| 実働時間 | 8:00 | 8:30 | 7:45 | 24:15 |
| 残業時間 | 0:00 | 0:30 | 0:00 | 0:30 |
日別の勤務時間を正しく計算できても、合計セルの書式が適切でなければ月間の結果を正確に確認できません。
SUM関数で月間合計を求める方法
それではまず、勤務時間を合計するSUM関数について解説していきます。
たとえばE2からE32に日ごとの実働時間が入っている場合、E33には次の式を入力します。
=SUM(E2:E32)
SUM関数は指定した範囲の数値を合計する関数で、時刻データにも使用できます。
残業時間がF列にあるなら、F33に=SUM(F2:F32)を入力します。
勤務していない日の実働時間が空欄でも、SUM関数は空欄を無視して集計します。
月間合計は、日ごとの値を手計算で足すのではなく、対象範囲を固定したSUM関数で自動集計する方法が確実です。
翌月の勤務表へコピーする場合は、合計範囲に合計セル自身を含めないよう注意しましょう。
24時間を超える合計の書式設定
続いては、勤務時間の合計を正しく表示するための書式設定を確認していきます。
合計セルを選択して右クリックし、セルの書式設定から表示形式を開きます。
ユーザー定義で[h]:mmを入力すると、24時間を超える合計もそのまま時間数として表示されます。
たとえば合計が168時間30分なら、168:30と表示されます。
通常のh:mm形式では、24時間ごとに時間部分が0へ戻るため、168:30が0:30のように見えてしまう場合があります。
月の総労働時間、総残業時間、有給時間の合計では、[h]:mm形式が必須と考えるとよいでしょう。
時間を小数で表示したいときは、合計時間に24を掛けると、たとえば8:30を8.5時間として扱えます。
条件付き書式による残業の確認
続いては、残業時間が長い日を見つけやすくする条件付き書式を確認していきます。
残業時間の列を選び、ホームタブの条件付き書式から新しいルールを選択します。
セルの値が次の値より大きいという条件を選び、基準値にTIME(2,0,0)相当の2:00を指定すると、2時間を超える残業を色で目立たせられます。
月60時間などの管理基準がある場合は、月間合計セルにも条件付き書式を設定すると確認しやすくなります。
色付けは警告のための補助であり、残業時間の定義や割増賃金の判定そのものを自動化するものではありません。
勤怠の締め処理前には、色が付いたセルだけでなく、欠勤、休暇、深夜勤務の行も合わせて確認しましょう。
【操作のポイント】合計セルの[h]:mm形式と条件付き書式を組み合わせると、月間の労働時間を正しく表示しながら、確認が必要な長時間勤務も見つけやすくなります。
まとめ エクセルで休憩時間と残業時間を含む勤務時間を合計する方法
Excelで勤務時間を計算するときは、退勤時刻から出勤時刻と休憩時間を引く基本式をまず作成します。
日をまたぐ夜勤では、=MOD(退勤時刻-出勤時刻,1)-休憩時間の考え方を使うと、負の時間を避けて実働時間を求められます。
残業時間は、=MAX(0,実働時間-所定労働時間)のように、0未満を表示しない式にすると見やすくなります。
月間の実働時間や残業時間をSUM関数で集計した後は、セルの表示形式を[h]:mmに変更してください。
勤務表では、時刻入力、休憩控除、日またぎ勤務、所定時間、合計表示のどこか一つでも設定を誤ると、集計結果に差が出ます。
まずは少人数分のサンプルで計算結果を確認し、問題がなければオートフィルで月間の勤務表へ広げていきましょう。
