【Excel】勤務時間を自動計算する方法｜休憩時間や残業計算まで対応

勤務表や勤怠管理をExcelで作成していると、

• 出勤・退勤時間から勤務時間を自動計算したい
• 休憩時間を差し引きたい
• 残業時間を自動表示したい
• 「24時間超え」で表示がおかしくなる
• 深夜勤務や日跨ぎ計算がうまくいかない

と悩むことはありませんか？

特に実務では、単純な引き算だけでは対応できないケースが非常に多くあります。
例えば「9:00〜18:00」なら簡単でも、休憩時間や残業時間、翌日退勤が加わると、一気に複雑になります。

さらに、Excelの時間は“数値”として管理されているため、表示形式を理解していないと「0.375」や「########」のような表示になることもあります。

この記事では、Excelで勤務時間を自動計算する基本方法から、休憩時間控除・残業時間計算・24時間表示・日跨ぎ対応まで、実務で使いやすい形で詳しく解説します。

✅ Excelで勤務時間を自動計算する基本方法

勤務時間計算は、Excel実務で最も使用頻度が高い時間計算の1つです。
しかし、「退勤−出勤」で終わりと思っていると、後からさまざまな問題に直面します。

特に初心者の方は、

• 時間なのに小数になる
• マイナス表示になる
• 24時間を超えると崩れる
• 合計だけおかしい

という状態になりやすいです。

Excelの時間計算は“表示形式”と“内部数値”の理解が重要です。
ここを最初に理解しておくことで、後から勤務表を作る際にかなり楽になります。

・勤務時間を計算する基本の数式

まずは最も基本的な勤務時間計算です。

例えば以下の表を用意します。

勤務時間を求めるには、C2セルへ次の数式を入力します。

"=B2-A2"

これだけで勤務時間を計算できます。

・時間表示を正しく設定する方法

数式入力後、「0.375」のような数値になる場合があります。

これは、Excel内部で時間を“1日＝1”として管理しているためです。

この場合は表示形式を変更します。

1. 勤務時間セルを選択
2. 右クリック
3. 「セルの書式設定」を開く
4. 「表示形式」→「時刻」
5. 「13:30」などを選択

これで時間表示になります。

・実務では「hh:mm」形式が最も使いやすい

勤務表では、

• 8:30
• 7:45
• 10:15

のような表示が一般的です。

そのため、ユーザー定義で

"hh:mm"

を使うケースが非常に多いです。

✅ Excelで休憩時間を差し引いて勤務時間を計算する方法

実務では、単純な勤務時間だけでは不十分です。
ほとんどの場合、休憩時間を差し引く必要があります。

しかしここでよくあるのが、

• 休憩時間を毎回手入力してミスする
• 時間形式が合わず計算がおかしくなる
• 休憩を引いたらマイナスになる

といった問題です。

特に勤務表は毎日使うため、“ミスしにくい設計”が非常に重要になります。

・休憩時間を差し引く基本数式

例えば以下の構成です。

実働時間を求めるには、D2セルへ次を入力します。

"=B2-A2-C2"

これで休憩時間込みの勤務計算ができます。

・固定休憩時間なら直接指定も可能

例えば「昼休憩1時間固定」の場合は、

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

でも計算可能です。

TIME関数は、

"=TIME(時,分,秒)"

で時間を作成できます。

・休憩時間が未入力の場合の対策

実務では空白セル対策も重要です。

例えば、

"=IF(C2="",B2-A2,B2-A2-C2)"

とすると、休憩未入力でも計算できます。

✅ Excelで残業時間を自動計算する方法

勤務時間管理では、残業計算も非常に重要です。

しかし、

• 法定時間を超えた分だけ計算したい
• 8時間超過だけ抽出したい
• マイナス残業を表示したくない

というケースが多く、単純計算では対応できません。

ここで重要になるのがIF関数です。

・8時間超過分を残業時間にする方法

例えば実働時間がD2セルにある場合、

"=IF(D2>TIME(8,0,0),D2-TIME(8,0,0),0)"

を入力すると、8時間超過分だけ表示できます。

・残業がない場合は空白表示にする

0:00表示を避けたい場合は、

"=IF(D2>TIME(8,0,0),D2-TIME(8,0,0),"")"

も便利です。

勤務時間表では、「0」やエラー表示が並ぶだけで、表全体が見づらくなることがあります。
特に実務では、“必要な情報だけを見せる設計”が重要であり、空白表示をうまく使うことで、視認性や確認効率を大きく改善できます。

残業計算だけでなく、Excel全体でエラーや不要表示をスッキリ整理したい方は、こちらの記事もあわせて確認してみてください。

→ 【Excel】「#N/Aならば空白」にする方法｜エラーを自動で非表示にし資料をきれいに整える基本

・残業計算で最も多い失敗

最も多いのが表示形式問題です。

例えば、

• 1:30 のはずが 0.0625
• 合計だけ崩れる

などです。

これは時間表示形式が統一されていないケースが非常に多いです。

✅ Excelで24時間以上の勤務時間を正しく表示する方法

長時間勤務や月間集計では、24時間超え問題が発生します。

例えば、

• 30時間
• 125時間
• 月間残業合計

などです。

ここで通常の「hh:mm」を使うと、24時間ごとにリセットされてしまいます。

・24時間以上を表示する設定

以下の表示形式を使います。

"[h]:mm"

これが非常に重要です。

・設定手順

1. 合計セルを選択
2. 右クリック
3. セルの書式設定
4. ユーザー定義
5. 以下を入力

"[h]:mm"

これで25:30のように表示できます。

・なぜこの設定が必要なのか

通常の「hh:mm」は時計表示です。

つまり、

• 25時間 → 1:00
• 48時間 → 0:00

のようにリセットされます。

実務では非常に危険なので注意が必要です。

✅ Excelで日跨ぎ勤務を計算する方法

夜勤や深夜勤務では、“翌日退勤”問題が発生します。

例えば、

通常計算ではマイナスになります。

ここで初心者が非常につまずきます。

・日跨ぎ対応の数式

以下を使用します。

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

これで翌日扱いできます。

・なぜ「+1」が必要なのか

Excelでは、

• 1日＝1
• 12時間＝0.5

として扱います。

つまり翌日は「+1日」が必要になります。

・深夜勤務表で重要な考え方

夜勤表では、

• 日付列を別管理
• 勤務日と退勤日を分離
• 深夜帯を別計算

する設計も重要になります。

✅ 勤務時間計算を見やすくする実務テクニック

勤務表は、計算だけでなく“見やすさ”も非常に重要です。

特に実務では、

• 入力ミス
• 空白
• 異常値
• 残業超過

を素早く見つける必要があります。

・条件付き書式で長時間勤務を色分けする

例えば、

• 10時間超え → 赤
• 残業あり → 黄色

などにすると視認性が向上します。

勤務時間表では、数値をただ並べるだけではなく、「どこに注意が必要なのか」を瞬時に判断できることが重要です。
特に長時間勤務や残業超過は、色分けによって視認性を大きく改善できます。

条件付き書式とIF関数を組み合わせて、“見た瞬間に判断しやすい表”を作る方法を詳しく知りたい方は、こちらの記事も参考にしてみてください。

→ 【Excel】条件付き書式とIF関数で視覚的に判断しやすくする方法とは？

・入力規則で時間形式を統一する

入力規則を使えば、

• 25:99
• 999
• 文字入力

などを防止できます。

勤務時間表では、入力ルールが統一されていないだけで、計算ミスや集計エラーが発生しやすくなります。
特に複数人で入力するファイルでは、「決まった形式を自動で入力・補助する仕組み」を作っておくことが、実務では非常に重要です。

入力ミスを減らし、Excel入力作業そのものを効率化したい方は、こちらの記事もあわせて確認してみてください。

→ 【Excel】特定の文字を自動入力する方法｜IF・入力規則・VLOOKUPで手間をなくす自動化

・テーブル化して自動集計しやすくする

Excelテーブル機能を使うと、

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

が簡単になります。

✅ VBAで勤務時間管理をさらに効率化する考え方

勤務表運用が大規模になると、手入力だけでは限界があります。

例えば、

• 月別勤務表自動生成
• CSV勤怠取込
• 残業超過アラート
• PDF出力
• 社員別集計

などです。

ここでExcelVBAを活用すると、

• 勤怠管理自動化
• 入力補助
• 集計高速化

が可能になります。

特に毎月同じ作業を繰り返している現場では、VBAとの相性が非常に良いです。

✅ まとめ：Excel勤務時間計算を正しく管理しよう

Excelでは、勤務時間を自動計算することで、勤怠管理を大幅に効率化できます。
ただし、時間計算は“表示形式”や“日跨ぎ”など、実務特有の注意点が非常に多い分野です。

特に勤務表は毎日使うため、最初の設計が非常に重要になります。

• "退勤−出勤"で基本勤務時間を計算できる
• 休憩時間は引き算で控除可能
• TIME関数で固定休憩も設定できる
• IF関数で残業時間を自動計算できる
• "[h]:mm"で24時間超え表示に対応できる
• 日跨ぎ勤務は「+1」で処理する
• 条件付き書式で視認性を向上できる
• VBAを活用すると大規模運用も効率化できる

勤務時間計算は、一度しっかり作っておくと長期間使える実務スキルです。

ぜひ今回の内容を活用して、見やすく・ミスしにくい勤務表を作成してみてください。