Excelで勤務時間を計算する方法｜日またぎの「#####」はMODで一発解決

Excelで勤務時間を出すには =終了時刻-開始時刻 と入力し、表示形式を [h]:mm にするだけです。夜勤など日をまたいで ##### になる場合は =MOD(終了-開始,1) に変えれば一発で解決します。休憩を引くなら引き算、時給を掛けるなら ×24 です。

とはいえ実務では「休憩を引きたい」「15分単位に丸めたい」と要件が増え、単純な引き算では足りなくなります。この記事では勤怠表の時刻計算を、基本から日またぎ・丸め・合計・コピペ用の完成形まで順に整理します。

Excelで勤務時間を計算する方法｜日またぎの「#####」はMODで一発解決のサンプル ▲ Date and Time サンプル

この記事の列の決めごと

数式をそのままコピーできるよう、以下で統一します。

列	内容	表示形式
A	開始時刻	`h:mm`
B	終了時刻	`h:mm`
C	休憩時間	`h:mm`
D	実働（時刻表示）	`[h]:mm`
E	実働（数値・時間数）	標準

基本は引き算＋表示形式の設定
日をまたぐ勤務はMODで吸収する
- IF関数で書くとこうなる
- MODとIFのどちらを使うべきか
休憩を引いて実働時間を出す
時給計算は「×24」で数値に変換
15分・30分単位に丸める前に知るべき労務ルール
合計が24時間を超えるときの落とし穴
これだけ覚えればいい完成形の式
よくあるつまずきと対処
残業・深夜割増・和文表示への応用

基本は引き算＋表示形式の設定

Excelの時刻は「1日＝1」の小数です。12:00は0.5、6:00は0.25。だから時刻同士はそのまま引き算できます。A2に9:00、B2に17:30を入れ、D2に=B2-A2とすれば8:30です。

ここで必ずやってほしいのが表示形式。D列を選択して Ctrl + 1 →「ユーザー定義」に [h]:mm と入力します。h:mm ではなく角括弧を付けるのがポイントで、角括弧なしだと25時間が「1:00」になってしまいます。

日をまたぐ勤務はMODで吸収する

22:00出勤・翌6:00退勤の夜勤では =B2-A2 が -16:00 となり、セルが ##### で埋まります。Excelは負の時刻を標準では表示できないためです。

=MOD(B2-A2, 1)

MODは「割り算の余り」。-16:00（-0.666…）を1で割った余りは0.333…、つまり8:00です。日またぎでもそうでなくても同じ式が使えるので、全行にコピーするのが最も安全です。

9:00〜17:30  → 8:30
22:00〜6:00  → 8:00

IF関数で書くとこうなる

=IF(B2<A2, B2+1, B2) - A2

「終了が開始より小さい＝日をまたいだなら1日足す」という素直な書き方で、結果はMOD版と一致します。Excelに詳しくない人がレビューする職場ではこちらが伝わりやすいでしょう。

MODとIFのどちらを使うべきか

	MOD版	IF版
式の短さ	◎ 短い	△ やや長い
意図の読みやすさ	△ 慣れが必要	◎ 明快
24時間ちょうどの勤務	✕ 0:00になる	✕ 0:00になる

両式とも、開始と終了が同じ時刻のとき0:00と判定します。25時間勤務なら「超過分の1:00」しか返りません。24時間以上の拘束を扱うなら、2026/9/17 22:00 のように日付込みのシリアル値で入力し、単純な =B2-A2 に切り替えてください。日付があれば分岐自体が不要です。

休憩を引いて実働時間を出す

休憩もC2に 1:00 と時刻で入れておけば、そのまま引けます。

D2: =MOD(B2-A2, 1) - C2

休憩を「60」のような分の数値で管理しているなら、/1440（1日の分数）で時刻に直します。

D2: =MOD(B2-A2, 1) - C2/1440

時給計算は「×24」で数値に変換

8:30に時給を掛けても正しい金額になりません。8:30の中身は0.354…だからです。時間数にするには24を掛けます。

E2: =D2*24            → 7.5（時間）
     =D2*24*1200       → 9000（円）

このセルの表示形式は「標準」に戻してください。時刻形式のままだと7.5が「1900/1/7 12:00」に化けます。

15分・30分単位に丸める前に知るべき労務ルール

先に結論から。1日ごとの労働時間の切り捨ては、就業規則に定めても認められません。 日本の労働基準法では1分単位の把握が原則で、例外として認められているのは「1か月の合計時間に生じた30分未満を切り捨て、30分以上を1時間に切り上げる」処理のみです（昭和63年の行政通達）。日々の15分丸めは賃金未払いにあたるおそれがあります。

そのうえで、シフト管理などで丸めが必要な場面での書き方です。

=CEILING(A2, "0:15")   8:52 → 9:00（出勤を切り上げ）
=FLOOR(B2, "0:15")     17:38 → 17:30（退勤を切り捨て）
=MROUND(B2, "0:15")    17:38 → 17:45（四捨五入）

"0:15" という文字列指定が不安なら TIME(0,15,0) でも同じです。丸めるときは差分ではなく開始・終了それぞれを丸めてから引くほうが集計のずれが出ません。

合計が24時間を超えるときの落とし穴

月間合計を =SUM(D2:D32) で出すと、160時間あるはずが「16:00」になることがあります。原因は表示形式が h:mm のままで、24時間ごとの繰り上がりが切り捨て表示されているだけ。合計セルにも [h]:mm を設定すれば 160:00 と出ます。

給与計算に渡すなら =SUM(E2:E32) で数値のまま 160 を出すほうが扱いやすいです。

これだけ覚えればいい完成形の式

勤怠表のD2・E2に入れるのはこの2本です。

D2: =IF(B2="","",MOD(B2-A2,1)-C2)      表示形式 [h]:mm
E2: =IF(B2="","",(MOD(B2-A2,1)-C2)*24) 表示形式 標準

IF(B2="","",…) を挟むのがテンプレ作りの肝です。これがないと未入力の行がすべて 0:00 で埋まり、印刷時に見苦しいうえ、平均値の計算も狂います。

よくあるつまずきと対処

##### になる — 列幅不足か負の時刻です。幅を広げても直らなければMOD版に差し替えます。

#VALUE! になる — 時刻が文字列です。9時00分 のような全角入力や前後の空白が原因。セルが左揃えなら文字列のサインなので、9:00 で入れ直すか =TIMEVALUE(A2) で変換します。

=HOUR(D2) が0になる — HOUR関数は「時刻の時部分」を返すため、25:00は1、24:00は0になります。時間数を出す目的にHOURは使えません。 ×24 を使ってください。分を含めて丸められる点でも ×24 が正解です。

1秒ずれる — 秒まで入力された時刻を [h]:mm で表示すると切り捨て表示になり、合計だけ合わないように見えます。入力側で =FLOOR(A2,"0:01") と1分単位に丸めて秒を捨てると安定します（"0:01" は1秒ではなく1分です）。

1904年日付システムに切り替えない — 負の時刻対策として紹介されますが、ブック全体の日付が4年ずれ、他ファイルとの連携で事故が起きます。MODで処理しましょう。

残業・深夜割増・和文表示への応用

残業時間：E列が数値なので =MAX(0, E2-8) で8時間超だけを取り出せます。
深夜割増：22:00〜翌5:00は25%以上の割増が必要です。深夜帯だけを切り出す式は条件が複雑になるため、シフトが固定なら深夜時間数を手入力する列を作るほうが確実です。
和文表示：表示形式に [h]"時間"mm"分" を入れると 8時間30分 と表示できます。値は時刻のままなので合計計算にもそのまま使えます。