【Excel】残業時間を自動計算する方法｜法定時間を考慮した管理表作成のコツ

勤怠管理表を作成していると、「残業時間を毎日手計算している」「法定労働時間を超えた時間だけを集計したい」「月末の集計作業をもっと楽にしたい」と感じることはありませんか。

Excelを活用すれば、出勤時間と退勤時間を入力するだけで残業時間を自動計算できます。さらに法定労働時間を考慮した計算式を設定しておけば、毎月の集計作業や確認作業も大幅に効率化できます。

この記事では、Excelで残業時間を自動計算する基本方法から、法定時間を考慮した実務向けの管理表作成まで詳しく解説します。

✅ Excelで残業時間を自動計算する基本的な考え方

残業時間の計算は単純に見えますが、実際には休憩時間や法定労働時間を考慮しなければ正しい集計になりません。

特に実務では「退勤－出勤」だけで計算してしまい、残業時間が実際より多くなるケースがよくあります。

また、時間表示形式の設定を間違えると計算結果が小数や日数で表示されることもあります。

最初に計算の仕組みを理解しておくことで、後から管理表を作り直す手間を防げます。

まずは残業時間の基本的な考え方を確認しておきましょう。

正しい設計を最初に行うことが重要です。

・残業時間計算の基本構成

例えば次のような表を作成します。

勤務時間は以下の計算式で求めます。

「=B2-A2-C2」

B2が退勤時間、A2が出勤時間、C2が休憩時間です。

・時間表示形式を設定する

計算結果のセルには以下の表示形式を設定します。

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

この設定をしておくことで24時間を超える集計にも対応できます。

勤務時間や残業時間を月単位で集計すると、24時間を超えるケースも珍しくありません。時間の合計が正しく表示されない原因や対処法については、【Excel】24時間以上を正しく表示する方法｜「25:00」表示の実務テクニック」で詳しく解説しています。

✅ Excelで法定労働時間を超えた残業時間を計算する方法

多くの人がここでつまずきます。

勤務時間を求めることと、残業時間を求めることは別の処理だからです。

例えば勤務時間が8時間30分の場合、残業時間は30分だけです。

しかし勤務時間そのものを残業時間として集計してしまう管理表も少なくありません。

法定労働時間を超えた部分だけを抽出する計算式を設定しておくことが重要です。

ここを理解すると実務で使える勤怠表になります。

・8時間超過分を残業時間として計算する

勤務時間がD2セルに入っている場合は次の式を使用します。

「=MAX(0,D2-TIME(8,0,0))」

計算結果は次のようになります。

MAX関数を使用することでマイナス値を防止できます。

・計算結果の表示形式を整える

残業時間セルにも以下の表示形式を設定します。

「[h]:mm」

これにより月間集計でも正しく表示できます。

残業時間の計算ミスは、勤務時間の算出方法に原因があるケースも少なくありません。休憩時間を含めた正しい勤務時間管理の方法を知りたい方は、【Excel】勤務時間を自動計算する方法｜休憩時間や残業計算まで対応も参考にしてみてください。

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

日別に残業時間を計算できても、月末集計が手作業では効率化できません。

実務では月間の合計残業時間を確認する場面が非常に多くあります。

給与計算や労務管理でも利用されるため、集計方法は覚えておきたいポイントです。

ここで設定しておけば毎月の作業負担が大幅に減ります。

特に人数が多い職場ほど効果を実感できます。

月単位の集計まで自動化しておきましょう。

・SUM関数で月間残業時間を集計する

残業時間がE列にある場合は以下の式を使用します。

「=SUM(E2:E32)」

表示形式を「[h]:mm」に設定すると、

40:30

75:45

120:15

のように表示できます。

月間残業時間を集計していると、「合計○分」を「○時間○分」で表示したい場面もあります。分単位のデータを時間表示へ変換する方法については、【Excel】分を時間表示に変換する方法｜90分を1:30に自動変換する実務テクニックで詳しく解説しています。

・部署別集計にも応用できる

部署ごとにシートを分けることで、

• 営業部
• 総務部
• 経理部

などの残業時間管理にも活用できます。

実務では部門別の労務管理で利用されるケースが多くあります。

✅ Excelで残業時間管理表を作る手順

勤怠表は後から列を追加すると管理しにくくなります。

最初に必要な項目を決めておくことが重要です。

実務では「とりあえず作った表」が後から複雑になり、修正できなくなるケースもあります。

管理表は見やすさも重要です。

誰が見ても計算内容が分かる構成にしておきましょう。

ここではおすすめのレイアウト例を紹介します。

・管理表のおすすめ構成

・作成手順

1. 日付列を作成する
2. 出勤・退勤入力欄を作成する
3. 休憩時間列を作成する
4. 勤務時間計算式を設定する
5. 残業時間計算式を設定する
6. 月間合計セルを作成する
7. 表示形式を整える

この構成であれば多くの業務で利用できます。

✅ Excelで残業時間計算をするときの注意点

残業時間の計算では意外な落とし穴があります。

特に時間計算特有の仕様を理解していないと、正しい結果になりません。

また夜勤や日付跨ぎ勤務がある場合はさらに注意が必要です。

実務で使う表ほど例外パターンが発生します。

最初から注意点を知っておくことでトラブルを防げます。

運用前に確認しておきましょう。

・休憩時間を差し引く

勤務時間から休憩時間を引かなければ正しい残業時間になりません。

・24時間超えの表示形式に注意する

通常の時刻表示では25時間以上になると表示が崩れます。

「[h]:mm」を利用しましょう。

残業時間や勤務時間を月単位で集計すると、24時間を超えたタイミングで表示が崩れることがあります。正しく「25:00」「100:30」のように表示する方法については、【Excel】24時間以上を正しく表示する方法｜「25:00」表示の実務テクニックで詳しく解説しています。

・日付を跨ぐ勤務に注意する

夜勤などの場合は単純な引き算では計算できないケースがあります。

勤務形態に合わせて設計する必要があります。

✅ Excel VBAで残業時間管理をさらに効率化する方法

毎月大量の勤怠データを扱う場合は、関数だけでなくVBAの活用も検討できます。

例えば複数シートの勤怠表を自動集計したり、月別レポートを自動作成したりできます。

実務では数十人分のデータを扱うケースも珍しくありません。

そのような環境では自動化の効果が大きくなります。

Excel関数で基礎を作り、必要に応じてVBAへ発展させるのがおすすめです。

業務効率化を進めたい方はVBA活用も検討してみましょう。

・VBAで実現できること

• 月間残業時間の自動集計
• 社員別集計表の作成
• CSV出力
• 部門別レポート作成
• 勤怠データの自動転記

大量データを扱う場合に特に効果を発揮します。

✅ まとめ：Excelで残業時間を自動計算して勤怠管理を効率化しよう

• 出勤・退勤・休憩時間から勤務時間を計算できる
• 「=MAX(0,D2-TIME(8,0,0))」で法定時間超過分を求められる
• SUM関数で月間残業時間を集計できる
• 表示形式は「[h]:mm」を使用する
• 実務では休憩時間や日付跨ぎ勤務に注意する
• VBAを活用すると大規模な勤怠管理も効率化できる

残業時間の管理は毎月発生する業務だからこそ、自動化による効果が大きい分野です。Excelの関数を活用して、正確で管理しやすい勤怠表を作成してみてください。