photo credit: WORK FOR WHAT ??? via photopin (license)
みなさん、こんにちは！
タカハシ(@ntakahashi0505)です。
Googleスプレッドシートを使って勤怠管理集計システムを作成していくシリーズです。
前回はこちらの記事。
IF文を駆使して、打刻時間から各時間帯の就業時間を求めることができました。
ここまで来たらあと一歩…なんて思ったら大間違いです。
法定超残業時間？休日労働？割増率は1.25？休日は1.35だっけ？深夜はプラス0.25？
…超ややこしいですね！
ということで、今回は本システムの最大の難関ともいえる各労働時間つまり時間内労働時間、時間外労働時間（残業時間）、休日労働時間、深夜労働時間の計算を進めていきましょう。
その際に一点お願いがあります。
本記事は残業時間や休日労働時間、深夜労働時間の計算方法の正しさを100％保証するものではありません。
労務に関するルールは企業によって異なる部分も多いですし、必ず社労士さんなどの専門家の確認をするなどをした上で運用をして下さいね。
残業時間計算の準備
さて、これまで作ってきたシートはこちらです。
R列からU列までにそれぞれ「時間内」「残業」「休日」「深夜」の列を作成しました。各日について、これらの時間の算出を目指していきます。
また、セルB11からB14までにそれぞれの合計値を算出していく予定です（前回記事までと項目名が異なっていますが修正をしました。スミマセン…！）。
さらに今回の目的を進めるにあたり、前準備として以下の修正や追加をしていきます。
法定労働時間の設定
まず、色々な計算の元となるパラメータを設定する「設定項目」シートですが、ここに「法定労働時間」という項目を追加しました。
今回の例では、所定労働時間は法定労働時間と同じとして、１日あたり8時間としています。
このあたりの考え方というか決めごとはなかなか難しくてですね…正しく説明できる自信がありませんので、知りたい方は専門家にお尋ねください。
また、早出開始時間ですがこれまでの記事では7時に設定していたのですが、5時に修正しました。深夜時間帯の計算でいうと、5時に設定をしておかないと正しく算出できません。
祝日一覧を作成
「祝日一覧」というシートを新規作成し、その年の土日以外の祝日・休日のリストを作成します。
「YYYY/MM/DD」の日付形式でA列にリストをしておきます。B列は休日の内容についてのコメントですが、わかりやすさのために入力しているだけでシステムとしてはなくても稼働します。
国民の祝日に加えて、年末年始など会社で定められている休日もリストに追加しておきます。
年がかわると、その年の分もリストに追加する必要があるのですが、一年に一回の作業ですのでご容赦下さい。
通常・残業・休日・深夜の時間計算
これで準備は完了しましたので、各項目について計算式を考えていきたいと思います。
休日労働時間の算出
通常勤務時間の労働時間から順番にいきたいところですが、わけあってまずはT列の休日労働時間の計算式から進めていきたいと思います。
今回は、「祝日一覧」に記載していある日の労働については全て休日労働とみなして、時間単価の1.35倍で計算するものとします。
それで、その算出方法ですが、
- 日付の曜日が土日である
- 日付が祝日一覧に含まれているものである
のいずれかが成立しているのであれば、その日の労働時間全体（Q列）、そうでないならゼロとなります。
ここまでを数式で表しますと例えばセルT3であれば
となります。
WEEKDAY関数は指定した日付の曜日を調べる関数です。第二引数で2を指定していますが、これはタイプ2を表していて、月曜日を1として順番に7までの数字で曜日を表すタイプです。
土曜日は6,日曜日は7になりますので、返り値が6以上であれば土日のいずれかだ、という判定になります。
詳細は以下の記事もご覧ください。
もう一つの条件は簡単ですね。COUNTIF関数を使っていますが、祝日一覧に同じ日付のものが1つ以上あればその日は祝日ということです。
ただ、このままですとD列が「–」（末尾が31日でない場合は、このように表現している）となっている場合は、エラー表示となってしまうので、エラーの判定をするIFERROR関数を使って
とします。
時間内労働時間を算出
続いて、時間内労働時間を算出していきます。ちょっと言葉がヘンかも知れませんが、法定時間内の労働時間ということです。
8時間を越えた場合は8時間、それに満たない場合はその時間となります。
時間内労働時間は通常の単価での計算となります。それをオーバーしたらいわゆる残業ということになり時間単価の1.25倍で計算をするということになります。
従いまして、例えばR3セルであれば
となりますね。
ただし、休日の場合は法定時間内だろうがなかろうが1.35で計算することになっていますので、この条件は営業日のみの適用となります。
ですから、計算式としては
として、休日のときはゼロになるようにしてあげます。先ほど計算式を作成したT列を活用して、該当日が休日なのかどうかを判断しています。
時間外労働時間を算出
ではS列の残業時間を算出していきます。
労働時間のうち、時間内労働時間でも休日労働時間でもなければそれが時間外労働つまり残業時間ということになり、その時間は時間単価×1.25で計算をすることになります。
合計の労働時間はQ列に出ていますから、時間内労働時間R列と休日労働時間T列を減算すればOKです。例えばセルS3であれば
となります。
というか、前回までで一生懸命L列とかO列を算出したんですがてんで使ってないですね…。まあ、出しておいたほうがわかりやすいので良しとしましょう。
深夜労働時間を算出
最後に深夜労働時間を計算します。
これまでの３項目、時間内労働、時間外労働、休日労働の和がその日の労働時間の合計になり、それぞれの時間単価を乗算してお給料が決まっていくのですが、深夜時間帯（22時から5時まで）の労働に関しては、それに追加して深夜労働時間×0.25に当たる金額を手当てとして支払う必要があります。
計算方法は既にK列とP列に算出されているので簡単です。例えばセルU3であれば
となります。
まとめ
これでスプレッドシート上に時間内労働時間、残業時間（時間外労働時間）、休日労働時間、深夜労働時間を算出することができました。
ここまで来ればあと一歩！
あとは合計を算出して微調整をしていけばシートのほうはOKですね。
次回、お伝えしていきたいと思います。
どうぞお楽しみに！
