残業代をエクセルで計算する

・2013年11月12日更新

休日労働をチェックボックスで切り替えられるようにしました。

 

・2013年10月26日更新

コメント欄でご指摘いただいた、休日労働が時間外労働としてもカウントされてしまうという不具合の対処法を赤字で追記しました。休日に関してはI列とJ列の数値を消してください。

 

・2013年6月3日更新

ご指摘をいただき深夜時間(早朝時間)の計算式を5:00以前に退勤した場合にも適用できるように修正しました。その部分は赤字で示してあります。

 

残業代の計算を頼まれることがたびたびあるので、計算方法をまとめておきます。退職してからでも請求できるので、興味のある方はこの記事を読んでまずは計算してみてください。

 

残業代計算ソフト(エクセルシート)「給与第一」|京都第一法律事務所(京都弁護士会所属)残業代が簡単に計算できるエクセルシート「給料ふえる君」(無料) – 沖縄県労連の活動に残業代を計算してくれるエクセルファイルが公開されていますが、自分で理屈をわかったほうがよいので、計算方法を解説しながらエクセルファイルを作ってみます。変形労働時間制や60時間以上の残業代5割増しといった例外的なルールは考慮していませんが、以下の理屈を理解すれば自分で応用できるはずです。

 

最初に私が作ったファイルを置いておきます。

 

zangyo_20130603

zangyo_20131112(休日労働チェックボックス作成前)

zangyo_20131112b(休日労働チェックボックス作成後)

 

背景が白の部分を入力すると自動的に計算されるようにしています。その仕組みは以下で解説します。

 

1.時給を計算する

残業代の計算はすべて時給ベースで行うので、最初に時給を算出する必要があります。もともと時給制の人は簡単ですね。月給制の人は月給を一月あたりの所定労働時間で割ります。年俸制ならば年俸を一年あたりの所定労働時間で割ります。手当ては給料に含めるものもあれば含めないものもあるので、詳しくは別のところで調べてください。私の作ったファイルではC3セル(各月1日のところ)に時給を入力すると、C列はそれぞれ1つ上のセルを参照して自動的に埋まるようになっています。もしも月の途中で時給が変わったならそこに入力するとそれ以降はその新しい時給になります。

WS000000

 

2.一日あたりの残業時間を計算する

一日あたりの法定労働時間は8時間です。まずは通常労働時間を求めましょう。退勤時刻から出勤時刻を引いて、そこから休憩時間を引けば労働時間が出ます。それが8時間を超えていれば残りは残業時間なので、通常労働時間は8時間になります。MIN関数で小さいほうを選ぶようにします。また、”8:00″と式中に表現しただけだと文字列になってしまうので、VALUE関数を使って時刻・時間形式(シリアル値)に変換します。念のためにセルを参照して計算している部分にもVALUE関数を使っておきます。これを第3行で具体的に計算すると、MIN(VALUE(E3-D3-F3),VALUE(“8:00”))です。残業時間はマイナスの値にならないようにMAX関数を用います。具体的にはMAX(VALUE(E3-D3-F3-“8:00”),VALUE(0))です。私の作ったファイルではD列の出勤時刻、E列の退勤時刻、F列の休憩時間を入力すると、自動的にI列の労働時間とJ列の一日あたり残業時間が算出されます。ちなみにエクセルでは「9:00」のように入力すると自動的に時刻として扱われるので便利です。

WS000001

 

3.一週あたりの残業時間を計算する

一週あたりの法定労働時間は40時間です。ここでは月曜日に週が始まり日曜日に週が終わると決めます。週の合計労働時間が40時間を超えると一週あたりの残業が発生します。ただしすでに一日あたりの残業としてカウントした分は週の計算をするときにはカウントしません。ですので具体的には月曜日から日曜日までの通常労働時間を合計して、そこから40時間を引くと、一週あたりの残業時間が計算できます。例えばSUM(I3:I9)-“40:00″といった具合です。これもマイナスの値になると困るのでMAX(VALUE(SUM(I3:I9)-“40:00″),VALUE(0))のようにMAX関数で処理します。これは日曜日ごとに計算すれば足りるので、他の曜日の部分では表示しないようにIF関数で場合分けしてIF(B9=”日”,MAX(VALUE(SUM(I3:I9)-“40:00″),VALUE(0)),””)のようにすると便利です。

WS000002

 

4.休日労働時間を計算する

休日労働は3割5分増しなので別に計算します。私の作ったファイルでは、もし休日労働があればI列で算出された労働時間をH列の休日労働時間に手入力で移してください。休日労働分は一週あたりの残業時間にはカウントしないので、I列とJ列の数値は消すようにしてください。

→この手作業をチェックボックスで実現できるようにしました(zangyo_20131112)。チェックを入れるとR列で休日かどうかの判定を行い、それに応じて値が入るようにIF関数で調整しました。チェックボックスの作成は面倒なのでマクロで行うようにしました。マクロを有効にしてから「休日チェックボックス作成」ボタンを押してください。チェックボックスそのものにはマクロが必要ありませんので、マクロが使えない環境ならチェックボックス作成後のファイル(zangyo_20131112b)でお試しください。

 

WS000003

 

5.深夜時間を計算する

深夜労働も合わせて計算します。22:00~5:00までが深夜労働です。前日から深夜労働に突入する場合と、その日から深夜労働を始める場合(0:00~5:00に出勤する場合)とに分けて考えます。

 

前日から深夜労働に突入する場合を考えましょう。例えば夜の8時から翌朝7時まで勤務したとすると、出勤時刻に20:00、退勤時刻に31:00と入力してください。休憩時間は通常通りに記入し、その内で深夜の時間帯の休憩時間があればその分を記入します。

 

まず退勤時刻が翌朝5時(29:00)を超える場合は29:00として計算するように関数を作ります。例えば11行目で行うなら、MIN(VALUE(E11),VALUE(“29:00”))です。退勤時刻から深夜休憩時間を引いたものが22:00を超えていたら深夜割増が発生します。そうでない場合は深夜割増は発生しません。これもMAX関数を用いて、かつ先ほどの計算式を活用すると、MAX(VALUE(MIN(VALUE(E11),VALUE(“29:00”))-G11-“22:00”),VALUE(0))となります。入れ子になっていてややこしいですね。

 

次にその日から深夜労働を始める場合、つまり早朝労働の場合です。早朝労働が発生する場合は5:00(5:00以前に退勤した場合はその退勤時間)-出勤時刻、そうでない場合は0という式を作ります。12行目で行うなら、MAX(VALUE(MIN(VALUE(E12),VALUE(“5:00”))-D12-G12),VALUE(0))ですね。ただし出勤時刻が0:00であるのと空白であるのとを区別しなければならないので、ISBLANK関数とIF関数を用いてIF(ISBLANK(D12),0,MAX(VALUE(“5:00”-D12-G12),VALUE(0)))とします。

 

1つ上の段落で計算したものと、2つ上の段落で計算したものの2つを足し合わせると深夜労働時間の出来上がりです。13行目であればMAX(VALUE(MIN(VALUE(E13),VALUE(“29:00”))-G13-“22:00”),VALUE(0))+IF(ISBLANK(D13),0,MAX(VALUE(“5:00”-D13-G13),VALUE(0)))という式になります。

WS000004

 

6.賃金をそれぞれ計算する

これで時間については準備ができたのでいよいよ賃金を計算します。これまで全て時刻・時間形式(シリアル値)で計算してきたので、そこから時間数を取り出す必要があります。エクセルのシリアル値は24時間を1とするような値なので、24倍すれば時間数になります。そうやって計算した時間数に休日労働なら時給の1.35倍、残業なら1.25倍をします。一週あたりの残業代はすでに通常賃金として計算しているので、割増分だけ計算します。深夜割増の部分もすでに賃金そのものは計算されているので、0.25倍して割増部分だけを計算します。3行目(4月1日)でそれぞれ計算するなら、休日賃金がH3*24*C3*1.35、通常賃金がI3*24*C3、一日あたり残業代がJ3*24*C3*1.25、一週あたり残業代は日曜日のところだけで表示するようにしてIF(B3=”日”,K3*24*C3*0.25,””)、深夜割増賃金はL3*24*C3*0.25です。金額なので表示形式を通貨にします。

WS000005

 

7.月の合計を計算する

先ほど計算した賃金を一月分合計します。SUMを用いれば簡単です。通常労働時間ならSUM(I3:I33)で、あとの部分も同じようにSUMで計算できます。一週あたりの残業代を計算するためには週の途中で月が変わったときの処理をしなければなりません。私の作ったファイルでは前月繰越、次月繰越ということで処理しています。月の最終日曜日の次の日からその月の末日までの労働時間を合計したものが次月繰越に入ります。その次の前月繰越の通常労働時間のセル(I2セル)にその先ほど計算した次月繰越分が入ります。

WS000006

 

以上が残業代の基本的な計算方法です。端数処理など細かいことは無視しましたが、この計算でそれほど間違いはないはずです。もし間違いに気づいた方がいらっしゃいましたらコメントをしてください。

 

 



  • 浅野直樹様

    初めてコメントさせていただきます。

    残業計算エクセルソフトの休日労働について、H列(休日労働時間)に9時間労働時間を入力すると、J列に自動で1時間入ってしまい、休日+時間外の割増になってしまう様なのですが・・・

    もし変更できるようでしたら、よろしくお願い致します。

    • 柴原様

      貴重なご指摘をありがとうございます。

      おっしゃるように、休日+時間外の割増になってしまうのはまずいです。休日に該当する部分はI列だけでなくJ列も手作業で消していただけますようお願いいたします。

      しかし休日の部分を手作業で全部さわるのも面倒なので、そのうち自動化するやり方を考えてみたいです。

  • 浅野直樹様

    早々にご対応いただき、ありがとうございました。

    休日の深夜に関して、L列に手入力する方法以外
    に楽な方法が有れば、教えていただけますでしょうか?

    よろしくお願い致します。

  • 柴原様

    遅くなってしまいましたが、休日労働の部分をチェックボックスで実現できるようにしました。チェックボックスの作成が面倒で、結局マクロに頼ることにしました。

    ご活用いただけますと幸いです。

  • 浅野直樹様

    連絡が遅くなりすみません。

    いろいろご対応いただきありがとうございました。

    活用させていただきます。

    柴原

  • 初めてコメントをさせていただきます。
    教えていただきことがあります。
    深夜時間を計算するにあたって

  •  中途半端でメールを送信してすみません。
     深夜時間の計算で教えていただきたいことがあります。
    MAX(VALUE(MIN(VALUE(E13),VALUE(“29:00″))-G13-”22:00″),VALUE(0))+IF(ISBLANK(D13),0,MAX(VALUE(“5:00″-D13-G13),VALUE(0))) を入力しましたが
    休みの日であっても計算されて5:00となります。
    休日は、出勤をしていませんので0:00となりません。

    • 松本様

      コメントありがとうございます。わかる範囲で対応を考えてみます。

      >休みの日であっても計算されて5:00となります

      それはもしかして休日労働時間のチェックボックスにチェックを入れても深夜労働時間が5:00になるということでしょうか。そうだとしたらむしろ正しい表示です。休日労働時間のチェックボックスは休日労働の割増を計算するためのものであり、労働をせずに休んだ日は出勤時刻と退勤時刻を空白にすることにしています。

      もし違うということであればもう少し詳しいご説明をお願いします。

  • はじめまして
    日付の場所で、オートフィルの使用は不可でしょうか?
    使用できるとかなり便利な使い方ができるのですが…

    • 日付の場所でオートフィルを使うことも可能です。もっと言うなら、この記事では完成品を配るというよりも考え方を示しているので、お好きなように改造していただいても構いません。


コメントを残す

メールアドレスが公開されることはありません。 が付いている欄は必須項目です