Excel約4分で読めます

Excelで勤務時間を計算する|日付をまたぐ場合

Excelで勤務時間を計算するを表したイメージ
記事の内容を表したイメージ(AI生成)

夜勤や遅番シフトの時間集計をExcelで行うとき、「23:00出勤→翌7:00退勤」のような日付をまたぐ勤務を単純に引き算すると、結果がマイナスになったり#######が表示されたりして困ることがあります。

これは、Excelが時刻を「その日の0:00からの経過割合」として扱っているために起きます。同じ日付の数値同士であれば引き算で正しく出ますが、日付をまたぐと退勤時刻の数値が出勤時刻より小さくなるため、引き算の結果が負になります。解決策は二つあります。①「日付+時刻」を1つのセルにまとめて入力する方法と、②時刻だけのセルのままIF関数で補正する方法です。どちらが自分の帳票に合うかで選んでください。

この記事では日付をまたぐ勤務時間の計算に絞り、数式の考え方・入力例・迷いやすい例外を順に示します。


なぜマイナスになるのか

Excelは日付と時刻を「シリアル値」という数値で管理しています。シリアル値とは、1900年1月1日を1として、そこからの日数を小数点つきで表したものです。時刻は1日を1として表した小数部分です。

たとえば「7:00」は0.2916…、「23:00」は0.9583…です。退勤(7:00)から出勤(23:00)を引くと 0.2916 − 0.9583 = −0.6666… となり、マイナスになります。Excelの標準設定では負の時刻を表示できないため、#######やエラーになります。

これを正しく扱うには「日付をまたいでいる」という情報をExcelに渡す必要があります。


方法①:日付と時刻を一緒に入力する

最も確実な方法です。セルに「2026/10/20 23:00」のように日付と時刻を一緒に入力すると、シリアル値に日付分が加算されるため、単純な引き算で正しい結果が得られます。

入力例(架空の例です)

表は横にスクロールできます

A(出勤)B(退勤)C(勤務時間)
22026/10/20 23:002026/10/21 07:00=B2-A2
32026/10/21 09:002026/10/21 18:00=B3-A3

C列のセルは表示形式を「[h]:mm」にしてください。[h]の角カッコは「24時間を超えても合計時間として表示する」指定です。h:mmのままだと48時間を超える集計が正しく表示されません。

表示形式の変更は「セルの書式設定」→「ユーザー定義」から行います。画面の表示は版や設定により異なります。

この方法の利点は数式がシンプルなことです。日をまたぐかどうかを数式で判定する必要がなく、後から見直すときも分かりやすいです。欠点は入力が少し手間になることで、「23:00」と打ちたいところを「10/20 23:00」と打つ必要があります。


方法②:時刻だけのセルをIF関数で補正する

既存の帳票が「出勤」列・「退勤」列ともに時刻だけで管理されていて、今さら日付を追加できない場合に使います。

考え方は「退勤時刻が出勤時刻より小さければ日付をまたいでいると判断し、1(=1日)を足す」というものです。

=IF(B2<A2, B2-A2+1, B2-A2)
  • B2<A2:退勤が出勤より小さい(日付をまたいでいる)場合
  • B2-A2+1:1日分(シリアル値で1)を足して補正
  • B2-A2:同じ日の勤務はそのまま引き算

入力例(架空の例です)

表は横にスクロールできます

A(出勤)B(退勤)C(勤務時間)
223:007:00=IF(B2<A2,B2-A2+1,B2-A2) → 8:00
39:0018:00=IF(B3<A3,B3-A3+1,B3-A3) → 9:00

C列の表示形式は同様に「[h]:mm」にします。

迷いやすい例外:深夜0:00をまたぐ場合

「22:00出勤→0:30退勤」のように、退勤が0:00直後になるケースです。0:30はシリアル値で0.0208…なので、22:00(0.9166…)より小さく、IF条件が正しく「またいでいる」と判断します。結果は2:30となり、正しく計算されます。この数式はこのケースに対応できています。

ただし「24時間を超える勤務」には対応できません。36時間連続勤務のような特殊なケースでは、方法①で日付つき入力を使うほうが安全です。


合計時間が正しく表示されないときの確認点

各行の勤務時間を正しく計算できても、合計セルで見た目がずれることがあります。よくある原因を二つ挙げます。

表示形式が「h:mm」になっている

合計が24時間を超えるとh:mm形式では「24時間を引いた余り」が表示されます。たとえば32時間は「8:00」と表示されます。合計セルの表示形式も必ず「[h]:mm」にしてください。

SUM関数は問題なく使えます

勤務時間の合計には通常の=SUM(C2:C10)が使えます。Excelは内部では小数(シリアル値)として合計しているため、表示形式さえ正しければ正確に集計されます。


二つの方法をどう選ぶか

表は横にスクロールできます

状況向いている方法
新しく帳票を作る、または列を追加できる方法①(日付+時刻入力)
既存の帳票を変えられない方法②(IF関数で補正)
24時間超の勤務がある方法①
入力者がExcel操作に不慣れ方法①(入力ミスが見つけやすい)

方法②は数式だけで対処できますが、「本当に日付をまたいでいるか」をセルを見ただけでは確認できません。入力担当者が別にいる場合は、出退勤の入力ルールを文書化しておくと誤入力を防ぎやすくなります。


次の行動

まず手元のファイルをコピーして作業用のバックアップを残してください。その上で、C2セルに今回の数式を入力し、「23:00→7:00」の1行で意図通りの「8:00」が出るかを確認します。確認できたら残りの行へ数式をコピーし、合計セルの表示形式が「[h]:mm」になっているかを最後にチェックしてください。

参考資料

この記事の本文はAI(Claude)が執筆しました。画面の表示や操作名は、アプリの版や設定によって異なります。実際の操作の前に元のファイルを複製し、公式のヘルプで最新の情報を確認してください。

記事の誤り・ファイルの不具合を知らせる · 修正記録

記事一覧へ