浅野直樹の学習日記

・２０１３年１１月１２日更新

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

 

・２０１３年１０月２６日更新

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

 

・２０１３年６月３日更新

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

 

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

 

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

 

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

 

zangyo_20130603

zangyo_20131112（休日労働チェックボックス作成前）

zangyo_20131112b（休日労働チェックボックス作成後）

 

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

 

１．時給を計算する

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

 

２．一日あたりの残業時間を計算する

一日あたりの法定労働時間は８時間です。まずは通常労働時間を求めましょう。退勤時刻から出勤時刻を引いて、そこから休憩時間を引けば労働時間が出ます。それが８時間を超えていれば残りは残業時間なので、通常労働時間は８時間になります。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」のように入力すると自動的に時刻として扱われるので便利です。

 

３．一週あたりの残業時間を計算する

一週あたりの法定労働時間は４０時間です。ここでは月曜日に週が始まり日曜日に週が終わると決めます。週の合計労働時間が４０時間を超えると一週あたりの残業が発生します。ただしすでに一日あたりの残業としてカウントした分は週の計算をするときにはカウントしません。ですので具体的には月曜日から日曜日までの通常労働時間を合計して、そこから４０時間を引くと、一週あたりの残業時間が計算できます。例えば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)),””)のようにすると便利です。

 

４．休日労働時間を計算する

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

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

 

 

５．深夜時間を計算する

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

 

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

 

まず退勤時刻が翌朝５時（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)))とします。

 

１つ上の段落で計算したものと、２つ上の段落で計算したものの２つを足し合わせると深夜労働時間の出来上がりです。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)))という式になります。

 

６．賃金をそれぞれ計算する

これで時間については準備ができたのでいよいよ賃金を計算します。これまで全て時刻・時間形式（シリアル値）で計算してきたので、そこから時間数を取り出す必要があります。エクセルのシリアル値は２４時間を１とするような値なので、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です。金額なので表示形式を通貨にします。

 

７．月の合計を計算する

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

 

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