Excelで残業時間を自動計算する方法｜8時間超過分だけ表示する勤務表の作り方

Excelで勤務時間を管理する場合、出勤時刻と退勤時刻を入力するだけで残業時間を自動的に計算できるようにすると、毎日の集計作業を大幅に効率化できます。この記事では、実働時間が8時間を超えた場合だけ残業時間として表示する方法や、勤務表を作成するときの注意点について詳しく解説します。

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

残業時間を求めるには、まず実際に働いた時間（勤務時間）を計算し、その時間から8時間を引きます。

例えば、出勤時刻が9時、退勤時刻が19時の場合、勤務時間は10時間です。この場合は8時間を超えた2時間が残業時間になります。

Excelでは時間を単なる数字ではなく「時刻データ」として扱うため、引き算を利用することで勤務時間を計算できます。

例えば、A2セルに出勤時間、B2セルに退勤時間が入力されている場合、8時間を超えた分だけ表示する数式は以下のようになります。

=IF(B2-A2>TIME(8,0,0),B2-A2-TIME(8,0,0),0)

この式では、まず「退勤時間－出勤時間」で勤務時間を計算し、それが8時間より長い場合だけ超過分を残業時間として表示します。

8時間以内の場合は残業なしとして「0」と表示されるため、通常勤務の日でも計算エラーになりません。

実際の勤務では昼休憩などがあるため、単純に退勤時間から出勤時間を引くだけでは正確な労働時間にならない場合があります。

例えば、9時出勤、18時退勤、休憩1時間の場合、拘束時間は9時間ですが、実働時間は8時間です。

休憩時間がC2セルに入力されている場合は、以下のように計算できます。

=IF(B2-A2-C2>TIME(8,0,0),B2-A2-C2-TIME(8,0,0),0)

このように休憩時間を引いてから8時間を超えているか判定すると、実際の残業時間に近い計算になります。

Excelで時間計算をするときは、セルの表示形式にも注意が必要です。

残業時間のセルを選択し、「セルの書式設定」から表示形式を「ユーザー定義」に変更して、[h]:mmを設定すると、24時間を超えるような長時間の集計でも正しく表示できます。

通常の「h:mm」形式では24時間を超えた時間がリセットされて表示されるため、月間残業時間などを集計するときには注意が必要です。

夜勤などで退勤時間が翌日になる場合、通常の引き算では正しく計算できないことがあります。

例えば、22時出勤で翌朝7時退勤の場合、退勤時間のほうが小さい値になるため、Excelではマイナス時間として扱われます。

この場合は、以下のような式を利用できます。

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

日付をまたぐ勤務では、勤務日の日付も入力して管理すると、より正確な勤怠表を作成できます。

毎月の勤怠管理を行う場合は、出勤時間、退勤時間、休憩時間、実働時間、残業時間の列を作成すると管理しやすくなります。

例えば、以下のような表にすると入力するだけで残業時間を自動計算できます。

このような形式にしておけば、毎日の入力作業だけで月間の残業時間集計まで簡単に行えるようになります。

Excelで8時間を超えた場合だけ残業時間を表示するには、IF関数とTIME関数を組み合わせる方法が便利です。

基本的な式は=IF(B2-A2>TIME(8,0,0),B2-A2-TIME(8,0,0),0)で、出勤時間と退勤時間から8時間を超えた分だけ自動計算できます。

休憩時間や日をまたぐ勤務がある場合は、条件を追加することでより正確な勤怠管理表を作成できます。Excelの時間計算機能を活用すると、毎月の残業集計も効率よく行えるようになります。