Excel で残業代を計算

こんにちは、スクリプト担当Fです。

一般的にサラリーマンの給与には、時間外の労働に対し残業代として割り当てられる手当が含まれます。とはいえ、残業代の計算は一体どのような仕組みになっているのでしょうか。サンプル問題をもとに Excel で計算してみようと思います。

問題
下記の条件で求められる割増賃金として、次のうち正しいものはどれか。

【条件】
〇1日所定労働日数:8時間
〇賃金締め日:毎月末日
〇賃金支給日:翌月25日
〇給与:時間給1,000円
〇勤怠状況
9月11日(水):残業42分
9月13日(金):残業90分
9月17日(火):残業164分
9月18日(水):残業95分
9月20日(金):残業175分
9月22日(日):休日出勤490分
9月25日(水):残業240分、残業(深夜)60分

①27,500円   ②27,750円   ③28,550円   ④28,750円
給与計算実務能力検定 サンプル問題より

● 残業代とは

そもそも残業代とはなんでしょうか? 労働基準法では1日8時間を超えた労働時間に対し、通常支払う賃金に一定の割増率で加算した賃金を支払わなければなりません。普段「残業代」と呼んでいるものはこの割増賃金のことを指します。

1日8時間を超えた労働時間

1日8時間を超えた労働時間については、通常支払われる賃金に25%以上の割増賃金が加算されます(労働基準法第三十七条第一項)。また、午後10時から午前5時までの労働時間については、さらに25%以上の割増賃金が加算されます(労働基準法第三十七条第四項)。

法定休日の労働時間

法定休日とは毎週少くとも一回は与えられる休日のことです(労働基準法第三十五条第一項)。日曜日が法定休日の場合が多いですがそうではない場合もあります。法定休日の労働時間は通常支払われる賃金に35%以上の割増賃金が加算されます。

● Excel で勤怠表を作成

まずは、サンプル問題の勤怠状況をもとに Excel で勤怠表を作成してみます。

9月勤怠表
 

勤怠状況から、出社時刻が9時、退社時刻は18時であることがわかります。

● 1日8時間を超えた労働時間を計算

では、1日8時間を超えた労働時間を計算してみます。労働時間が8時間を超えていたら退社時刻から出社時刻と休憩時間を引いた時間を算出する数式です。
例として、9月11日(E列12行目のセル)に数式を入力しています。

 =IF(D12B12C12>1/3,D12B12C12-1/3,””)) 

退社時刻から出社時刻と休憩時間を引いた時間を算出

退社時刻から出社時刻と休憩時間を引いた時間を算出

「1/3」とは1日の時間計算のうち8時間をあらわしています(24時間×1/3=8時間) 。

休みの日は残業時間の計算が不要です。出社時刻が空白であれば計算しません。

 =IF(ISBLANK(B12),””,IF(D12B12C12>1/3,D12B12C12-1/3,””)) 

出社時間が空白であれば計算しない

出社時間が空白であれば計算しない

ISBLANK 関数で出社時刻が空白かどうかを判断しています。

土日も計算しないように数式を追加します。

 =IF(OR(WEEKDAY(A12,2)>=6,ISBLANK(B12)),””,IF(D12B12C12>1/3,D12B12C12-1/3,””)) 

土日も計算しない

土日も計算しない

WEEKDAY 関数で土日かどうか、もしくは ISBLANK 関数で出社時刻が空白かどうかを判断しています。

この数式をE列に反映させることで1日8時間を超えた労働時間が計算できます。

1日8時間を超えた労働時間を計算

● 深夜残業を計算

午後10時から午前5時までの労働時間については、さらに25%以上の割増賃金が加算されます。退社時刻が午後10時を超えた分の労働時間を計算してみます。
例として、9月25日(F列26行目のセル)に数式を入力しています。

 =IF(D26>TIME(22,0,0),D26-TIME(22,0,0),””) 

退社時刻が午後10時を超えた分の労働時間を計算

退社時刻が午後10時を超えた分の労働時間を計算

TIME 関数で退社時刻が午後10時を超えているかどうかを判断しています。

この数式をF列に反映させることで深夜残業が計算できます。

深夜残業を計算

● 法定休日の労働時間を計算

法定休日の労働時間は通常支払われる賃金に35%以上の割増賃金が加算されます。サンプル問題での法定休日である日曜日の労働時間を計算してみます。
例として、9月22日(G列23行目のセル)に数式を入力しています。

 =IF(AND(WEEKDAY(A23)=1, B23&C23&D23<>””),D23B23C23,””) 

法定休日である日曜日の労働時間を計算

WEEKDAY 関数で日曜日であるかどうかを判断し、かつ出社時刻/休憩時間/退社時刻が空白ではないことを確認しています。

この数式をG列に反映させることで法定休日である日曜日の労働時間が計算できます。

法定休日である日曜日の労働時間を計算

 

● 合計時間を計算

残業時間、深夜残業時間、法定休日労働時間がそれぞれ算出されました。各列を月ごとにまとめて計算してみます。
例として、残業時間の合計(E列32行目のセル)に数式を入力してみます。

 =SUM(E2:E31

時間の合計

時間の合計

ここで気をつけないといけないのは、時間を合計した際に24時間以上になる場合です。セルの書式設定→ユーザー設定で [h]:mm と設定します。は時間をあらわしますがそのままでは24時が0時となってしまいます。累計時間をあらわすために [h] と表記します。

累計時間をあらわすために [h]:mm とする

累計時間をあらわすために [h]:mm とする

数式とセルの書式設定を残業時間、深夜残業時間、法定休日労働時間それぞれの合計セルに反映させることで合計時間が計算できます。

セルの書式設定

● 割増賃金の計算

残業時間、深夜残業時間、法定休日労働時間はそれぞれ、月の合計のうち1時間未満の時間(分)が法律により端数処理を認められています。

30分以上→切り上げ
30分未満→切り下げ

例として、残業時間の合計(E列32行目のセル)に対し端数処理をしてみます。まずは分を60で割って四捨五入します。

 =ROUND(MINUTE(E32)/60,0) 

分を60で割って四捨五入

分を60で割って四捨五入

MINUTE 関数で分を取得し60で割った数値を ROUND 関数で四捨五入しています。

時間は合計時間をそのまま取得します。

 =HOUR(E32

合計時間

合計時間

HOUR 関数で時間を取得できますが、このままでは24時間が0にリセットされてしまうので累計時間を計算する工夫が必要です。

 =DAY(E32)*24+HOUR(E32

累計時間を計算

累計時間を計算

DAY 関数で日数を算出し24を掛けて時間を足す処理をしています。

この端数処理を行った時間外労働時間の計算と、時間給と割増率を掛ける数式を一度で行えるように書き換えるとこうなります。

 =(DAY(E32)*24+HOUR(E32)+ROUND(MINUTE(E32)/60,0))*$D35*E34 

数式を一度に行う

数式を一度に行えるように書き換える

残業時間、深夜残業時間、法定休日労働時間のそれぞれにこの数式を反映させることで割増賃金が計算できます。

割増賃金が計算できる

割増賃金が計算できる

あとはこの3つを足し算すると…

3つを足し算

3つを足し算

残業代(割増賃金)が算出されます!

残業代が計算できる!

おつかれさまでした!

 

よって、サンプル問題の正解は③ということになります。

● 月給制の場合の割増賃金の基礎になる賃金

サンプル問題は時給制でしたが、月給制の場合では1時間あたりの賃金はいくらとなるのでしょうか? 月給制での割増賃金の基礎になる賃金計算方法は、

月給÷1年間における1か月平均所定労働時間

となります。ここでいう月給には家族手当/扶養手当/通勤手当/住宅手当などは含まれません(一律支給の場合を除く)。

例えば、年間所定休日122日、1日の所定労働時間が8時間、月給の内訳が基本給215,000円、役職手当8,000円、住宅手当20,000(一律全員に支給)、通勤手当15,000円の場合で Excel で計算してみます。
例として、列Bにそれぞれ値と数式を入れてます。

月給制の場合の割増賃金の基礎になる賃金

月給制の場合の割増賃金の基礎になる賃金

1年間における1か月平均所定労働時間は、365(日)から年間所定休日を引いた日数に1日の労働時間を掛け、12(か月)で割ります。

=(365-B1)*B2/12

 =(365-B1)*B2/12 

月給には基本給、役職手当が含まれます。また、住宅手当も一律支給なので対象に含みます。通勤手当は含まれません。

基本給+役職手当+住宅手当

基本給+役職手当+住宅手当

月給を1年間における1か月平均所定労働時間で割ると、月給制の場合での割増賃金の基礎になる賃金が算出されます。

割増賃金の基礎になる賃金

割増賃金の基礎になる賃金

● 時間外労働の計算方法は人によって変わります

今回の記事で取り上げた Excel の例はあくまでサンプル問題用であり、実際は1日の所定労働時間が8時間ではない場合や、変形労働時間制を採用している会社の場合、また会社規模によって時間外労働の計算方法はさまざまです。さらに管理職につきましては時間外手当や休日手当の支払いの義務はありません。月合計1時間未満の端数処理も切り下げ/切り上げはせず、Excel や給与計算ソフトを使用すれば1分単位の計算もたやすいものです。というわけで計算方法は会社や人によって変わってくるということをご了承ください。