Excelで勤務時間や休憩時間を計算するときは、時刻の引き算だけでは意図した結果にならないことがあります。
たとえば、始業時刻から終業時刻を引けば在社時間は求められますが、休憩時間を差し引かなければ実働時間にはなりません。
また、深夜勤務のように日付をまたぐケースでは、表示形式や数式の設定によって結果が正しく見えない場合もあります。
この記事では、出勤・退勤・休憩の記録から勤務時間を計算する基本式、休憩時間の扱い、24時間を超える合計時間の表示方法までを解説します。
勤務時間の基本式は、退勤時刻－出勤時刻－休憩時間です。
時間の合計は表示形式を［h］:mmに変更すると、24時間を超えても正しく確認できます。
時刻を数値として扱うExcelの仕組みを押さえれば、勤怠表やシフト表の作成にも応用しやすくなります。
エクセルで勤務時間を計算する基本数式
| 出勤時刻 | 退勤時刻 | 休憩時間 | 実働時間 |
|---|---|---|---|
| 9:00 | 18:00 | 1:00 | =B2-A2-C2 |
それではまず、勤務時間を求める基本の数式について解説していきます。
出勤時刻と退勤時刻の入力
勤怠表では、1行目を見出しとして、A列に出勤時刻、B列に退勤時刻、C列に休憩時間、D列に実働時間を入力する構成が使いやすいでしょう。
A2セルには9:00、B2セルには18:00、C2セルには1:00のように、時刻として認識される形式で入力します。
時刻は文字列ではなく、Excelが時間として認識する値で入力することが大切です。
セルを選択して数式バーを確認し、左寄せではなく通常の時刻表示になっていれば、多くの場合は正しく入力されています。
「9時」のような曖昧な入力ではなく、9:00のようにコロンを含めた時刻を入力しましょう。
入力後に表示が9:00:00になる場合もありますが、セルの表示形式を時刻に設定すれば問題ありません。
実働時間を求める引き算
D2セルに=B2-A2-C2と入力すると、退勤時刻から出勤時刻と休憩時間を差し引いた実働時間を計算できます。
この式では、B2-A2で在社時間を求め、さらにC2を引くことで休憩を除いた勤務時間を算出します。
=B2-A2-C2
退勤時刻－出勤時刻－休憩時間
9:00から18:00までの9時間に対し、1時間の休憩を引くと、結果は8:00になります。
数式は1件目のD2セルに入力した後、セル右下のフィルハンドルを下へドラッグすれば、他の日付の行にもコピーできます。
参照セルは行番号に合わせて自動的に変わるため、D3セルでは=B3-A3-C3となります。
時間表示の確認
計算結果が0.333333のような小数で表示された場合は、計算が失敗したわけではなく、セルの表示形式が標準になっている可能性があります。
Excelでは1日を1として時刻を管理しているため、8時間は24時間の3分の1として保存されています。
D列を選択し、ホームタブの表示形式から時刻を選ぶと、結果を8:00のように読みやすく表示できます。
計算用の値と画面上の見た目は別に管理されている点が、時間計算の基本です。
【操作のポイント】勤務時間の結果セルは、数式入力後に必ず時刻表示へ変更して確認します。
休憩時間を差し引く計算方法
| 出勤時刻 | 休憩開始 | 休憩終了 | 退勤時刻 | 実働時間 |
|---|---|---|---|---|
| 8:30 | 12:00 | 13:00 | 17:30 | =D2-A2-(C2-B2) |
続いては、休憩の開始時刻と終了時刻を使って実働時間を計算する方法を確認していきます。
休憩時間を別セルで入力する方法
休憩時間が毎日同じ場合は、C列に1:00のような休憩時間そのものを入力する方法が簡単です。
実働時間の数式は、先ほどと同じ=B2-A2-C2で計算できます。
昼休みが1時間と決まっている職場では、C列に1:00を入力して下方向へコピーすると、日々の入力を減らせます。
休憩時間を固定値にする場合でも、休憩を取らなかった日や短縮した日は必ず実績に合わせて修正しましょう。
休憩時間を数値の1だけで入力すると1日として扱われるため、必ず1:00のように入力します。
30分休憩なら0:30、45分休憩なら0:45と入力するのが基本です。
休憩開始と終了から求める方法
休憩の開始時刻をB列、終了時刻をC列に記録する場合は、休憩時間を=C2-B2で求められます。
出勤時刻をA列、退勤時刻をD列に入力し、実働時間をE列に表示するなら、E2には=D2-A2-(C2-B2)と入力します。
=D2-A2-(C2-B2)
退勤時刻－出勤時刻－（休憩終了時刻－休憩開始時刻）
この形式なら、日によって休憩時間が変わる場合も、開始と終了を入力するだけで正確に反映できます。
外出や中抜けがあるときは、休憩時間の欄を増やすか、休憩時間を合計したセルを用意して実働時間の式から引くと整理しやすくなります。
休憩時間が複数回ある日の扱い
午前休憩と昼休憩、午後休憩のように複数の休憩がある場合は、それぞれの休憩時間を合計してから在社時間より差し引きます。
たとえば、C列からE列に各休憩時間を入力した場合、F2セルへ=B2-A2-SUM(C2:E2)と入力すれば実働時間を計算できます。
SUM関数を使うと、休憩回数が多い表でも数式を複雑にしすぎずに済みます。
【操作のポイント】休憩の記録方法は、固定時間か開始・終了時刻かを先に統一してから数式を作成します。
日付をまたぐ勤務時間の計算
| 出勤時刻 | 退勤時刻 | 休憩時間 | 実働時間 |
|---|---|---|---|
| 22:00 | 6:00 | 1:00 | =MOD(B2-A2,1)-C2 |
続いては、夜勤などで退勤時刻が翌日になる場合の時間計算を確認していきます。
深夜勤務で発生する表示の問題
22:00に出勤し、翌朝6:00に退勤する場合、単純に=B2-A2と入力すると、マイナスの時間となって正しく表示されないことがあります。
これは退勤時刻の6:00が、同じ日の22:00より小さい値として扱われるためです。
日付をまたぐ勤務では、時刻だけではなく翌日分であることを数式に考慮させる必要があります。
夜勤の行だけ別の数式を入れる方法もありますが、日勤と夜勤が混在する勤怠表では、どちらにも対応できる式が便利です。
MOD関数を使った勤務時間の数式
日付またぎに対応させるには、実働時間のセルに=MOD(B2-A2,1)-C2と入力します。
=MOD(B2-A2,1)-C2
時刻差を1日単位で循環させ、そこから休憩時間を差し引く数式です。
MOD関数は、計算結果が負の時間になったときにも、1日を単位として正しい時間差へ戻す働きをします。
22:00から翌日6:00までは8時間であり、休憩が1:00なら実働時間は7:00です。
MOD関数の第2引数を1にすることで、24時間を1日として処理できます。
日付も入力する勤怠表の計算
出勤日と退勤日を別々に管理する場合は、日付と時刻を組み合わせて計算すると、より確実に勤務時間を求められます。
たとえば、A列に出勤日、B列に出勤時刻、C列に退勤日、D列に退勤時刻を入力する場合、勤務時間は=(C2+D2)-(A2+B2)-E2で計算できます。
日付と時刻はどちらもExcel内部では連続した数値であるため、加算して日時として扱えます。
連続する夜勤や長時間勤務を管理する表では、日付列を用意する方法が特に安全です。
【操作のポイント】夜勤がある表ではMOD関数を使うか、出勤日と退勤日を分けて記録します。
勤務時間の合計と表示形式
| 日付 | 実働時間 |
|---|---|
| 4月1日 | 8:00 |
| 4月2日 | 7:30 |
| 合計 | =SUM(B2:B3) |
続いては、毎日の勤務時間を合計し、月間の労働時間として表示する方法を確認していきます。
SUM関数による時間の合計
日ごとの実働時間がD2からD31に入力されている場合、合計欄には=SUM(D2:D31)と入力します。
通常の数値と同じようにSUM関数で合計できますが、時間の表示形式を変更しないと、24時間を超えた部分が見えなくなることがあります。
月間勤務時間の集計では、合計値そのものよりも表示形式の設定が重要です。
=SUM(D2:D31)
日ごとの実働時間を月単位で合計する数式です。
二十四時間を超える時間の表示
合計セルを選択し、セルの書式設定からユーザー定義を開き、表示形式に［h］:mmを設定します。
角かっこ付きのhを使うと、24時間を超えても累計時間として表示できます。
たとえば、合計が160時間の場合、通常のh:mmでは16:00のように表示されることがありますが、［h］:mmなら160:00と表示されます。
月間合計や残業時間の集計では、必ず［h］:mmの形式を利用しましょう。
【操作のポイント】合計時間のセルだけは、時刻ではなく累計時間用の［h］:mmを設定します。
残業時間を別列で計算する方法
所定労働時間が8時間の場合、実働時間が8時間を超えた分を残業時間として計算できます。
実働時間がD2セルにあるなら、残業時間のE2セルには=MAX(D2-TIME(8,0,0),0)と入力します。
=MAX(D2-TIME(8,0,0),0)
実働時間から8時間を引き、マイナスの場合は0時間とする数式です。
MAX関数を組み合わせると、所定時間未満の日に負の残業時間が表示されることを防げます。
時間計算で起こりやすいエラーの対処
| 表示内容 | 主な原因 | 確認する項目 |
|---|---|---|
| ##### | 負の時間または列幅不足 | 日付またぎと列幅 |
| 0:00 | 文字列入力 | 入力形式 |
| 小数表示 | 表示形式が標準 | 時刻形式 |
続いては、勤務時間の計算で表示されやすいエラーと見直し方を確認していきます。
シャープ記号が表示される原因
セルに#####と表示されるときは、列の幅が不足している場合と、負の時刻が発生している場合があります。
まず列見出しの境界をドラッグして列幅を広げ、それでも解消しない場合は、退勤時刻と出勤時刻、日付またぎの数式を確認します。
夜勤で単純な引き算をしていると、負の時刻となって#####が表示されやすくなります。
日付またぎの行にはMOD関数を使うことで、多くのケースを解決できます。
時刻が文字列として扱われる場合
セルに9:00と見えていても、先頭にアポストロフィが付いていたり、全角記号が混じっていたりすると、文字列として扱われることがあります。
文字列の時刻は計算に使えないため、引き算の結果が0:00になったり、VALUE!エラーになったりします。
入力し直すか、VALUE関数やTIMEVALUE関数で時刻値へ変換する方法があります。
=TIMEVALUE(A2)
文字列として入力された時刻を、計算可能な時刻の値へ変換します。
コピー元のデータを貼り付けた後に計算できないときは、文字列化を疑うのが近道です。
休憩控除の重複を防ぐ確認
休憩開始と終了の差をすでに計算しているのに、さらに固定の1時間を引くと、休憩時間を二重に差し引いてしまいます。
数式を作る前に、休憩時間を固定値で入力する方式なのか、開始・終了時刻から計算する方式なのかを確認しましょう。
勤怠表を複数人で共有する場合は、入力欄の見出しを明確にして、計算列を保護する運用も有効です。
【操作のポイント】エラーが出たときは、数式だけでなく時刻の入力形式と休憩の扱いを順番に確認します。
まとめ エクセルで勤務時間・休憩時間を計算する方法
Excelで勤務時間を計算する基本は、退勤時刻から出勤時刻と休憩時間を引くことです。
休憩時間が固定なら=B2-A2-C2、休憩開始と終了を記録するなら=D2-A2-(C2-B2)のように、表の構成に合わせて数式を選びます。
夜勤など日付をまたぐ勤務には=MOD(B2-A2,1)-C2を使うと、マイナス表示を避けながら実働時間を求められます。
月間の勤務時間を合計するときは、SUM関数を使ったうえで、表示形式を［h］:mmへ変更することも忘れないようにしましょう。
時刻の入力、休憩の控除、日付またぎ、合計表示という4点を確認すれば、Excelの勤怠計算は安定します。
まずは少ない行数のサンプル表で数式を確認し、問題なく計算できたら実際の勤務表へオートフィルで展開していきましょう。
