Excel関数で残業時間を計算する方法をお探しですね。

広告

Excelで「8時間以上の残業」と「深夜残業」を自動計算する方法【初心者向けテンプレート】

Excelで勤怠表を作るとき、多くの人がつまずくのが「8時間を超えた残業時間」と「22時〜翌朝5時の深夜残業」の計算です。

単純に退勤時刻から出勤時刻を引くだけでは、休憩時間や日をまたぐ勤務、深夜帯の計算、月の合計表示などに対応できません。

この記事では、ExcelのIF関数を使って「8時間を超えた残業時間」と「深夜残業時間」を自動で計算する数式を、初心者でもすぐに使える形で紹介します。

1. Excelで残業時間を計算する前に知っておきたい基本

Excelで時刻を扱うとき、まず知っておきたいのは「Excelでは1日=1」として時間を管理しているということです。

たとえば8時間は1日の3分の1なので「1/3」とも表せますが、わかりやすさを考えると「TIME(8,0,0)」や「”8:00″」と書くほうが実用的です。

残業時間を計算するには、出勤時刻・退勤時刻・休憩時間から実際に働いた時間(実働時間)を出して、その実働時間が8時間を超えた分だけを取り出します。

ここで注意したいのが「8時間以上」という言葉の意味です。

一般的には「8時間以上働いたら残業」と言われますが、法律上の考え方では、1日8時間を**超えた部分**が残業になります。

つまり、ちょうど8時間働いた場合は残業0時間で、8時間30分働いたら30分が残業時間です。

Excelの数式でも、この考え方に合わせて「8時間を超えた分」を自動計算するようにしておくと、給与計算や勤怠管理に使いやすくなります。

以下のような列構成にすると、数式を管理しやすくなります。

| セル | 内容 | 入力例 |
|—|—|—|
| A2 | 日付 | 2026/4/1 |
| B2 | 出勤時刻 | 9:00 |
| C2 | 退勤時刻 | 18:30 |
| D2 | 休憩時間 | 1:00 |
| E2 | 実働時間 | 数式で計算 |
| F2 | 8時間超の残業時間 | 数式で計算 |
| G2 | 深夜労働時間 | 数式で計算 |
| H2 | 残業開始時刻 | 数式で計算 |
| I2 | 深夜残業時間 | 数式で計算 |

実働時間を出す基本の式は、退勤時刻から出勤時刻と休憩時間を引く形です。

ただし、夜22:00に出勤して翌朝6:00に退勤するような日をまたぐ勤務では、退勤時刻のほうが出勤時刻より小さく見えるため、そのまま引き算するとエラーやマイナスになってしまいます。

そこで、退勤時刻が出勤時刻より小さい場合は、退勤時刻に「1日分」を足して計算します。

“`excel
=IFERROR(IF(C22. IF関数で8時間を超えた残業時間を自動計算するテンプレート

実働時間がE2に計算できたら、次は「8時間を超えた部分」だけをF2に表示します。

一番わかりやすいIF関数の形は、実働時間が8時間より大きければ実働時間から8時間を引いて、そうでなければ空欄にする数式です。

空欄にしておくと、残業がない日のセルがすっきりして、月末の確認もしやすくなります。

“`excel
=IF(E2>TIME(8,0,0),E2-TIME(8,0,0),””)
“`

この式は、E2が8時間を超えているかどうかを判定して、超えている場合だけ残業時間を返します。

たとえば実働時間が8:30ならF2は0:30、実働時間が7:45なら空欄になります。

空欄ではなく「0:00」を表示したい場合は、最後の「””」を「0」に変更してください。

ただ、表の見やすさを考えると空欄のままがおすすめです。

よりエラーに強くしたい場合は、IFERROR関数を組み合わせます。

出勤時刻や退勤時刻がまだ入力されていない日でもエラー表示を防げるので、日々入力していく勤怠表ではこちらのほうが使いやすいです。

“`excel
=IFERROR(IF(E2>TIME(8,0,0),E2-TIME(8,0,0),””),””)
“`

また、残業時間を給与計算に使う場合は、時間表示のままではなく「時間数」に変換する必要があります。

Excelでは1時間が「1/24」として扱われるので、残業時間に時給を掛けるときは「×24」を使います。

たとえばF2の残業時間に時給1,200円、割増率1.25を掛けるなら、次のように計算できます。

“`excel
=F2*24*1200*1.25
“`

ただし、残業代の計算では就業規則や雇用契約、変形労働時間制、フレックスタイム制、法定休日の扱いなどによって条件が変わることがあります。

この記事のテンプレートは、1日8時間を基準にした一般的な固定労働時間制を想定しています。

実際に使う場合は、自社のルールや専門家に確認したうえで、計算方法を調整してください。

3. 22時〜翌朝5時の深夜労働と深夜残業を分けて計算する数式

深夜労働時間は、原則として22時から翌朝5時までの時間帯に働いた時間です。

ここで難しいのは、勤務が日をまたぐケースだけでなく、20:00〜2:00、23:00〜6:00、1:00〜4:00のように、勤務時間と深夜帯の重なり方がいろいろあることです。

単純に「退勤時刻が22時を超えたら退勤時刻−22時」とするだけでは、翌朝5時までの上限や早朝勤務に対応できません。

G2に深夜労働時間を出す場合は、勤務時間と深夜帯が重なっている部分だけを取り出します。

以下の数式は、深夜帯を「22:00〜翌5:00」と「0:00〜5:00」の重なりとして扱って、日をまたぐ勤務にも対応しやすい形にしています。

“`excel
=IFERROR(MAX(0,MIN(C2+(B2>C2),1+TIME(5,0,0))-MAX(B2,TIME(22,0,0)))+MAX(0,MIN(C2+(B2>C2),TIME(5,0,0))-MAX(B2,0)),””)
“`

この式では、`C2+(B2>C2)` の部分で日をまたぐ勤務を判定しています。

B2>C2がTRUEの場合、ExcelではTRUEが1として扱われるので、退勤時刻に1日分を足して計算できます。

たとえば20:00出勤、2:00退勤なら、2:00を翌日の2:00として扱って、22:00〜2:00の4時間を深夜労働時間として算出できます。

ところで、「深夜労働時間」と「深夜残業時間」は同じではありません。

深夜労働時間は22時〜翌5時に働いた時間全体を指しますが、深夜残業時間は、そのうち**8時間を超えた残業部分**が深夜帯にかかっている時間です。

たとえば18:00〜翌4:00で休憩1時間の場合、実働は9時間です。

この場合、深夜労働は22:00〜4:00の6時間ですが、8時間を超えた残業部分は最後の1時間なので、深夜残業は3:00〜4:00の1時間として考えます。

この区別をするには、まずH2に「残業開始時刻」を補助列として作ります(この列は非表示にしても構いません)。

休憩時間が勤務の途中に取られる前提で、出勤時刻に休憩時間と8時間を足して、実働8時間を超えた時点を残業開始時刻として扱います。

“`excel
=IF(F2>0,B2+D2+TIME(8,0,0),””)
“`

次に、I2に深夜残業時間を算出する数式を入れます。

残業開始時刻から退勤時刻までの範囲と、22:00〜翌5:00の深夜帯が重なっている部分だけを取り出す考え方です。

“`excel
=IFERROR(IF(F2=0,””,MAX(0,MIN(C2+(B2>C2),1+TIME(5,0,0))-MAX(H2,TIME(22,0,0)))+MAX(0,MIN(C2+(B2>C2),TIME(5,0,0))-MAX(H2,0))),””)
“`

このテンプレートを使うと、普通の深夜労働と、8時間を超えた後に発生する深夜残業を分けて集計できます。

ただし実際には、休憩を取った時間帯によって残業開始時刻が変わる場合があります。

たとえば休憩が深夜帯の途中にある勤務や、休憩を複数回取るシフトでは、もっと細かい設計が必要です。

正確性を高めたい場合は、休憩開始・休憩終了の列を設けて、勤務区間を分割して計算する方法も検討してください。

4. 月合計・給与計算まで使える勤怠表にするための注意点

日ごとの残業時間や深夜残業時間を計算できても、月の合計で表示がおかしくなると実務では使いにくくなります。

残業時間の合計はF列、深夜労働時間の合計はG列、深夜残業時間の合計はI列をSUM関数で集計します。

たとえばF2:F32の残業時間を合計するなら、次のように入力します。

“`excel
=SUM(F2:F32)
“`

この合計セルも、表示形式は「**[h]:mm**」にしてください。

月間の残業が24時間を超えることは珍しくないので、普通の時刻形式では正しい累計時間に見えない場合があります。

給与計算に使う場合は、合計時間に24を掛けて小数の時間数に変換します。

たとえば月の残業時間合計がF33、時給が1,200円なら、残業代は「F33×24×1200×1.25」のように計算できます。

深夜割増を計算する場合は、通常賃金に加えて深夜割増分を別計算にするか、深夜時間全体に1.25倍を掛けるかで設計が変わります。

多くの勤怠表では、通常分の賃金は実働時間で計算して、深夜労働については割増分の0.25だけを追加する形にすると重複計算を防ぎやすくなります。

たとえばG33が深夜労働時間の月合計で、時給が1,200円なら、深夜割増分は次のように計算できます。

“`excel
=G33*24*1200*0.25
“`

また、労働時間の端数処理には注意が必要です。

日々の勤務時間を5分単位、10分単位、15分単位で一律に切り捨てる運用は、未払い賃金につながる恐れがあります。

原則として労働時間は1分単位で把握して、月単位の時間外労働、深夜労働、休日労働の端数処理についても、認められる範囲を確認したうえで行う必要があります。

Excelで簡単に丸められるからといって、安易にROUND関数やFLOOR関数を使わないことが大切です。

最後に、Excel勤怠表は便利ですが、数式の上書き、セル参照のずれ、担当者しか仕組みを理解していない属人化が起きやすい点もあります。

従業員数が少なくて勤務形態がシンプルなうちはExcelでも十分に運用できますが、変形労働時間制、複数休憩、法定休日の判定、シフト別時給、承認フローまで管理する場合は、勤怠管理システムの導入も現実的な選択肢です。

まずは今回のテンプレートで「実働時間」「8時間超の残業時間」「深夜労働時間」「深夜残業時間」を自動化して、自社の勤怠ルールに合わせて無理のない形で整えていきましょう。

広告