夜勤や遅番シフトの時間集計を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(勤務時間) | |
|---|---|---|---|
| 2 | 2026/10/20 23:00 | 2026/10/21 07:00 | =B2-A2 |
| 3 | 2026/10/21 09:00 | 2026/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(勤務時間) | |
|---|---|---|---|
| 2 | 23:00 | 7:00 | =IF(B2<A2,B2-A2+1,B2-A2) → 8:00 |
| 3 | 9:00 | 18: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」になっているかを最後にチェックしてください。
参考資料
- IF 関数 | Microsoft Support · support.microsoft.com 参照日 2026-10-23
- SUM 関数 | Microsoft Support · support.microsoft.com 参照日 2026-10-23
この記事の本文はAI(Claude)が執筆しました。画面の表示や操作名は、アプリの版や設定によって異なります。実際の操作の前に元のファイルを複製し、公式のヘルプで最新の情報を確認してください。
