【Excel】深夜勤務時間を計算する方法｜日をまたぐ時間計算の注意点とは

夜勤やシフト勤務の勤怠管理をExcelで行っていると、「勤務時間がマイナスになる」「深夜時間だけ集計したい」「日付をまたぐと計算がおかしくなる」といった問題に直面することがあります。

特に22:00～翌5:00のような深夜勤務は、通常の時間計算とは少し考え方が異なります。そのため、単純な引き算だけでは正しい勤務時間を求められないケースも少なくありません。

この記事では、Excelで深夜勤務時間を計算する方法を中心に、日をまたぐ時間計算の考え方や実務でよくあるミスへの対処法まで詳しく解説します。

✅ Excelで深夜勤務時間を計算する基本的な考え方

深夜勤務の計算で最も多い失敗は、「時間だけを引き算している」ことです。

例えば22:00から翌6:00まで勤務した場合、見た目では8時間勤務ですが、Excelは日付をまたいだことを考慮しなければ正しく計算できません。

また、勤怠管理では総勤務時間と深夜勤務時間を分けて管理することもあります。

そのため、まずはExcelが時間をどのように扱っているかを理解することが重要です。

ここを理解しておくと、後々の計算式作成が格段に楽になります。

・Excelの時間データの仕組み

Excelでは時間を1日＝1として管理しています。

例えば、

• 12:00 → 0.5
• 06:00 → 0.25
• 18:00 → 0.75

という数値として保存されています。

そのため時間の計算は実際には数値計算です。

・日をまたぐ勤務が難しい理由

例えば、

• 開始時刻：22:00
• 終了時刻：06:00

の場合、

単純に

"=終了時刻-開始時刻"

を実行するとマイナス値になります。

これはExcelが同じ日の6:00と判断してしまうためです。

✅ 深夜勤務時間を正しく計算する方法

深夜勤務を正しく計算するためには、終了時刻が開始時刻より小さい場合を考慮する必要があります。

この方法を覚えておけば、多くの夜勤シフトに対応できます。

実務でも最も利用されている計算方法の一つです。

・日をまたぐ勤務時間を計算する数式

開始時刻がA2、終了時刻がB2の場合

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

を使用します。

例えば、

• 22:00
• 翌6:00

の場合、

結果は8:00になります。

・表示形式を変更する

計算結果が「0.333333」などで表示される場合は表示形式を変更します。

1. セルを選択
2. Ctrl＋1を押す
3. 表示形式を選択
4. ユーザー定義を選択
5. "[h]:mm"を設定

これで勤務時間として表示できます。

深夜勤務時間を計算した後は、給与計算や工数管理のために「分単位」で集計したい場合もあります。時間データを数値化して扱う方法については、【Excel】時間を分に変換する方法｜勤務時間や作業時間を正しく集計する実務テクニックで詳しく解説しています。

✅ 深夜時間（22時～5時）だけを計算する方法

実務では総勤務時間だけでなく、深夜割増対象時間も計算することがあります。

給与計算やシフト管理では非常に重要な項目です。

しかし、単純な勤務時間計算とは考え方が異なります。

重複する時間帯だけを抽出する必要があります。

・深夜時間の考え方

一般的な深夜時間帯は、

• 22:00
• ～翌5:00

です。

例えば、

• 21:00～翌6:00勤務

の場合、

勤務時間は9時間ですが、

深夜時間は7時間になります。

・深夜時間を求める数式

開始時刻をA2、終了時刻をB2とした場合の一例です。

"=MAX(0,MIN(B2+(B2<A2),TIME(29,0,0))-MAX(A2,TIME(22,0,0)))"

複雑に見えますが、

• 深夜開始22:00
• 深夜終了翌5:00

との重複時間だけを取得しています。

給与計算表などで活用できます。

✅ 勤怠管理表で深夜勤務を集計する方法

深夜勤務を管理する場合、毎日計算するのは大変です。

そのため、勤務表を作成して自動集計できる仕組みを作ることが重要です。

実務では月単位で管理するケースがほとんどです。

後から集計できる形を意識して作成しましょう。

・勤務表の基本構成

以下のような構成がおすすめです。

この形式なら月間集計もしやすくなります。

・月間深夜時間を集計する

深夜時間列がE列の場合

"=SUM(E:E)"

で合計できます。

勤務日数が多くても自動集計できます。

勤怠管理表を実務レベルで活用するためには、深夜勤務時間だけでなく残業時間もあわせて管理することが大切です。法定労働時間を考慮した計算方法は、【Excel】残業時間を自動計算する方法｜法定時間を考慮した管理表作成のコツも参考にしてください。

✅ 深夜勤務時間計算でよくあるミス

時間計算は一見簡単そうですが、実務ではさまざまなミスが発生します。

特に勤怠管理では給与にも影響するため注意が必要です。

ここでは代表的な失敗例を紹介します。

・終了時刻が翌日扱いになっていない

最も多いミスです。

22:00～6:00を

"=B2-A2"

だけで計算すると正しい結果になりません。

IF関数を使って日跨ぎを判定しましょう。

・表示形式が時間になっていない

数式が正しくても表示形式が標準だと、

0.333333

のように表示されます。

"[h]:mm"

に変更しましょう。

・24時間超の勤務が正しく表示されない

月間集計では24時間を超えることがあります。

通常の

"h:mm"

では25時間以上を正しく表示できません。

必ず

"[h]:mm"

を使用しましょう。

勤務時間や残業時間を月単位で集計すると、24時間を超えるケースは珍しくありません。25時間や100時間といった累計時間を正しく表示する設定方法については、【Excel】24時間以上を正しく表示する方法｜「25:00」表示の実務テクニックで詳しく解説しています。

✅ 深夜勤務計算をさらに効率化する方法

勤務人数が増えると計算式の管理も大変になります。

そのため、Excelの便利機能を活用して自動化することが重要です。

集計ミスを防ぎながら作業時間も削減できます。

・テーブル機能を利用する

勤務表をテーブル化すると、

• 数式自動コピー
• フィルター
• 集計

が簡単になります。

シフト管理では特に便利です。

・条件付き書式を活用する

深夜勤務が発生した行だけ色付けすると視認性が向上します。

管理者が確認しやすくなるためおすすめです。

勤怠管理表では、深夜勤務時間が長い日や残業時間が多い日を一目で確認できると管理効率が向上します。条件付き書式を活用した視覚的な管理方法は、【Excel】数値の大小を色で直感的に比較する方法｜条件付き書式活用術も参考にしてください。

・Excel VBAで自動化する

従業員数が多い場合はVBAも有効です。

例えば、

• 勤務時間計算
• 深夜時間計算
• 月間集計
• 帳票出力

まで自動化できます。

毎月同じ集計を繰り返している場合は、大きな業務効率化につながります。

✅ まとめ：Excelで深夜勤務時間を正しく計算しよう

深夜勤務時間の計算は、通常の時間計算よりも注意が必要です。

特に日をまたぐ勤務では、単純な引き算だけでは正しい結果になりません。

今回紹介した考え方を理解しておくことで、勤怠管理や給与計算を正確に行えるようになります。

• Excelでは時間を数値として管理している
• 日をまたぐ勤務はIF関数で判定する
• 深夜時間は22時～5時の重複時間を計算する
• 表示形式は"[h]:mm"を利用する
• 月間集計にはSUM関数が便利
• テーブル化すると管理しやすい
• VBAを活用するとさらに効率化できる

深夜勤務の計算を正しく理解しておけば、勤怠管理表や給与計算表の精度が向上します。まずは勤務時間計算から作成し、徐々に深夜時間集計まで自動化していきましょう。