【Excel】シフト勤務の時間計算を自動化する方法｜日をまたぐ勤務にも対応

シフト勤務の勤怠管理をExcelで行っていると、「勤務時間が合わない」「夜勤だけ計算結果がおかしい」「日付をまたぐとマイナス表示になる」といった問題に悩むことがあります。

特に飲食店や小売業、医療・介護業界などでは、22時から翌朝までの勤務や交代制勤務が多く、単純な引き算だけでは正しく勤務時間を計算できません。

しかし、Excelの関数を活用すれば、日をまたぐシフトにも対応した勤務時間の自動計算が可能です。

この記事では、シフト勤務の時間計算を自動化する方法から、夜勤対応の計算式、休憩時間を考慮した勤務時間計算、実務で使いやすい勤怠表の作り方まで詳しく解説します。

✅ Excelでシフト勤務の時間計算を自動化する基本方法

シフト表を作成するとき、多くの人は開始時間と終了時間を入力して手計算で勤務時間を求めています。しかし人数が増えると計算ミスが発生しやすくなります。

特に月末の集計時に誤りが見つかると修正に時間がかかります。

また、勤務時間を毎回計算する作業は非常に非効率です。

最初に正しい計算式を設定しておけば、時間を入力するだけで自動集計できるようになります。

ここを理解しておくと、後で紹介する夜勤計算もスムーズに設定できます。

まずは最も基本となる勤務時間計算から確認しましょう。

・シフト勤務の基本的な表を作成する

以下のような表を用意します。

勤務時間には次の数式を入力します。

"=B2-A2"

・勤務時間を正しく表示する

計算結果セルを選択します。

1. セルを右クリック
2. セルの書式設定を選択
3. ユーザー定義を選択
4. "[h]:mm" を設定

これで勤務時間が正しく表示されます。

・休憩時間を差し引く場合

休憩時間が1時間の場合は次の数式を使用します。

"=B2-A2-TIME(1,0,0)"

実務では休憩時間列を作成する方が管理しやすくなります。

✅ Excelで日をまたぐシフト勤務を計算する方法

夜勤や深夜勤務では、単純な引き算では正しい結果になりません。

例えば22時から翌朝7時まで勤務した場合、Excelは退勤時刻の方が小さいと判断します。

その結果、マイナス時間となり正しく計算できません。

夜勤勤務がある職場では、この問題を知らないまま運用しているケースも少なくありません。

後から勤怠データを修正するのは非常に手間がかかります。

まずは日をまたぐ勤務専用の計算式を覚えておきましょう。

・夜勤勤務を計算する数式

以下のデータを例にします。

勤務時間セルには次の数式を入力します。

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

結果は9:00となります。

・数式の仕組み

IF関数で、

• 退勤時間が出勤時間より小さい
• 日付をまたいでいる

と判断しています。

その場合は1日分を加算して計算します。

実質的には次のような考え方です。

22:00 → 24:00 = 2時間

0:00 → 7:00 = 7時間

合計9時間

という仕組みです。

・24時間超えにも対応できる

この方法は深夜勤務だけでなく、

• 宿直勤務
• 当直勤務
• 長時間シフト

にも利用できます。

夜勤や当直勤務などを集計していると、勤務時間の合計が24時間を超えるケースもあります。そんなときに表示が崩れてしまう場合は、【Excel】24時間以上を正しく表示する方法｜「25:00」表示の実務テクニックもあわせてご覧ください。

✅ Excelで休憩時間を含めたシフト勤務を自動計算する方法

勤務時間だけでなく休憩時間も考慮しなければ実際の労働時間は求められません。

休憩時間を固定値で管理している会社もありますが、実務では勤務ごとに異なることもあります。

そのため休憩時間を別列で管理する方法がおすすめです。

あとから勤務ルールが変更された場合でも柔軟に対応できます。

また、休憩時間を明確に管理すると勤怠監査にも役立ちます。

ここでは実務向けの管理方法を紹介します。

・休憩時間列を作成する

・実働時間の計算式

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

この方法なら、

• 休憩45分
• 休憩60分
• 休憩90分

などにも対応できます。

・休憩時間を自動設定する例

例えば6時間以上勤務なら1時間休憩の場合は、

"=IF(D2>=TIME(6,0,0),TIME(1,0,0),0)"

と設定できます。

休憩時間の計算ができるようになったら、次は勤務時間全体の自動集計にも挑戦してみましょう。残業時間や実働時間までまとめて管理したい方は、【Excel】勤務時間を自動計算する方法｜休憩時間や残業計算まで対応で詳しい手順を解説しています。

✅ Excelでシフト勤務の月間集計を行う方法

勤務時間を日ごとに計算できても、月末集計が大変では意味がありません。

実務では月間労働時間や残業時間を集計する場面が多くあります。

手入力で集計すると計算ミスの原因になります。

また従業員数が増えるほど確認作業も増加します。

月間集計まで自動化できれば管理負担を大きく削減できます。

ここでは勤務時間の合計方法を紹介します。

・勤務時間を合計する

勤務時間がD列にある場合

"=SUM(D2:D31)"

を使用します。

・表示形式を変更する

合計セルの表示形式を

"[h]:mm"

に設定します。

・なぜ表示形式が重要なのか

通常の時刻表示では、

25時間

↓

1:00

と表示されてしまいます。

月間集計では必ず

"[h]:mm"

を使用しましょう。

・残業時間も計算できる

例えば所定時間が160時間の場合は、

"=合計勤務時間-TIME(160,0,0)"

の考え方で残業時間を管理できます。

✅ Excelでシフト表作成をさらに効率化する実務テクニック

勤務時間の自動計算ができるようになると、次は管理しやすいシフト表を作ることが重要になります。

見やすさを考慮しないシフト表は入力ミスの原因になります。

また、管理者以外も利用する場合は操作性も重要です。

実務では入力しやすさと集計しやすさの両方を意識する必要があります。

ここでは運用しやすいシフト表作成のポイントを紹介します。

・入力規則を設定する

入力規則を利用すると、

• 時刻入力ミス
• 不正な値の入力

を防げます。

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

夜勤シフトを色分けすると確認しやすくなります。

例えば、

• 夜勤：青
• 日勤：緑

などに設定できます。

勤務時間の計算だけでなく、異常値や夜勤シフトを色分けして管理すると、シフト表の使いやすさが大きく向上します。条件付き書式とIF関数を組み合わせた実践的な活用方法は、【Excel】条件付き書式とIF関数で視覚的に判断しやすくする方法とは？で詳しく紹介しています。

・VBAで完全自動化する

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

例えば、

• シフト表の自動作成
• 月間集計の自動化
• 個人別勤務時間集計
• 勤怠レポート作成

などを自動化できます。

Excel関数で運用を始め、管理規模が大きくなった段階でVBAを導入すると効率的です。

✅ まとめ：Excelでシフト勤務の時間計算を自動化しよう

シフト勤務の時間計算は、関数を活用することで大幅に効率化できます。

• "=B2-A2" で基本的な勤務時間を計算できる
• 日をまたぐ勤務は "IF関数" を使う
• 休憩時間は別列で管理すると便利
• 月間集計は "SUM関数" で自動化できる
• 表示形式は "[h]:mm" を利用する
• 夜勤や交代制勤務にも対応できる
• 大規模運用ではVBAによる自動化も有効

一度シフト表を整備しておけば、毎月の勤怠集計作業を大幅に削減できます。まずは日をまたぐ勤務の計算式から導入し、実務に合ったシフト管理表を作成してみてください。