Googleスプレッドシートで時間内・時間外(残業)・休日・深夜の労働時間を計算する


みなさん、こんにちは!
タカハシ(@ntakahashi0505)です。

Googleスプレッドシートを使って勤怠管理集計システムを作成していくシリーズです。

前回はこちらの記事。

GoogleスプレッドシートでIF文を超絶駆使して通常・残業・深夜の就業時間を求める
Googleスプレッドシートを使った勤怠管理集計システムを作っています。今回は開始時刻と終了時刻のデータを使って通常時間、残業時間、深夜時間のそれぞれの就業時間を算出する計算式を作成していきます。

IF文を駆使して、打刻時間から各時間帯の就業時間を求めることができました。

ここまで来たらあと一歩…なんて思ったら大間違いです。

法定超残業時間?休日労働?割増率は1.25?休日は1.35だっけ?深夜はプラス0.25?

…超ややこしいですね!

ということで、今回は本システムの最大の難関ともいえる各労働時間つまり時間内労働時間、時間外労働時間(残業時間)、休日労働時間、深夜労働時間の計算を進めていきましょう。

その際に一点お願いがあります。

本記事は残業時間や休日労働時間、深夜労働時間の計算方法の正しさを100%保証するものではありません。

労務に関するルールは企業によって異なる部分も多いですし、必ず社労士さんなどの専門家の確認をするなどをした上で運用をして下さいね。

スポンサーリンク

残業時間計算の準備

さて、これまで作ってきたシートはこちらです。

スプレッドシートの勤務管理シート

R列からU列までにそれぞれ「時間内」「残業」「休日」「深夜」の列を作成しました。各日について、これらの時間の算出を目指していきます。

また、セルB11からB14までにそれぞれの合計値を算出していく予定です(前回記事までと項目名が異なっていますが修正をしました。スミマセン…!)。

さらに今回の目的を進めるにあたり、前準備として以下の修正や追加をしていきます。

法定労働時間の設定

まず、色々な計算の元となるパラメータを設定する「設定項目」シートですが、ここに「法定労働時間」という項目を追加しました。

スプレッドシートで法定労働時間を設定

今回の例では、所定労働時間は法定労働時間と同じとして、1日あたり8時間としています。

このあたりの考え方というか決めごとはなかなか難しくてですね…正しく説明できる自信がありませんので、知りたい方は専門家にお尋ねください。

また、早出開始時間ですがこれまでの記事では7時に設定していたのですが、5時に修正しました。深夜時間帯の計算でいうと、5時に設定をしておかないと正しく算出できません。

祝日一覧を作成

「祝日一覧」というシートを新規作成し、その年の土日以外の祝日・休日のリストを作成します。

スプレッドシートに祝日一覧を作成

「YYYY/MM/DD」の日付形式でA列にリストをしておきます。B列は休日の内容についてのコメントですが、わかりやすさのために入力しているだけでシステムとしてはなくても稼働します。

国民の祝日に加えて、年末年始など会社で定められている休日もリストに追加しておきます。

年がかわると、その年の分もリストに追加する必要があるのですが、一年に一回の作業ですのでご容赦下さい。

通常・残業・休日・深夜の時間計算

これで準備は完了しましたので、各項目について計算式を考えていきたいと思います。

休日労働時間の算出

通常勤務時間の労働時間から順番にいきたいところですが、わけあってまずはT列の休日労働時間の計算式から進めていきたいと思います。

今回は、「祝日一覧」に記載していある日の労働については全て休日労働とみなして、時間単価の1.35倍で計算するものとします。

それで、その算出方法ですが、

  • 日付の曜日が土日である
  • 日付が祝日一覧に含まれているものである

のいずれかが成立しているのであれば、その日の労働時間全体(Q列)、そうでないならゼロとなります。

ここまでを数式で表しますと例えばセルT3であれば

=IF(OR(WEEKDAY(D3,2)>=6,COUNTIF(‘祝日一覧’!A:A,D3)>0),Q3,0)

となります。

WEEKDAY関数は指定した日付の曜日を調べる関数です。第二引数で2を指定していますが、これはタイプ2を表していて、月曜日を1として順番に7までの数字で曜日を表すタイプです。

土曜日は6,日曜日は7になりますので、返り値が6以上であれば土日のいずれかだ、という判定になります。

詳細は以下の記事もご覧ください。

スプレッドシートでスケジュール管理表の土日に条件付き書式で色を付けて見やすくする方法
エクセルでスケジュール管理されている方を見かけます。ただ、土日の欄を青や赤で毎月塗り直すのが手間ではありませんか?日付と曜日も毎月面倒ですね。 今回はその手順を、スプレッドシートで自動化してみたいと思います。難しいコードなどは使いません。いくつかの関数と条件付き書式を使った簡単な方法でご案内いたします。

もう一つの条件は簡単ですね。COUNTIF関数を使っていますが、祝日一覧に同じ日付のものが1つ以上あればその日は祝日ということです。

ただ、このままですとD列が「–」(末尾が31日でない場合は、このように表現している)となっている場合は、エラー表示となってしまうので、エラーの判定をするIFERROR関数を使って

=IFERROR(IF(OR(WEEKDAY(D3,2)>=6,COUNTIF(‘祝日一覧’!A:A,D3)>0),Q3,0),0)

とします。

エクセルで計算結果にエラーを表示しないIFERROR関数の使い方
Excelでよく見かけるエラー表示…IFERRORという関数を使うことで綺麗に回避することができます。それとともに、関数の効率の良い便利な入力の仕方にも触れていますので合わせてマスターしましょう。

時間内労働時間を算出

続いて、時間内労働時間を算出していきます。ちょっと言葉がヘンかも知れませんが、法定時間内の労働時間ということです。

8時間を越えた場合は8時間、それに満たない場合はその時間となります。

時間内労働時間は通常の単価での計算となります。それをオーバーしたらいわゆる残業ということになり時間単価の1.25倍で計算をするということになります。

従いまして、例えばR3セルであれば

=IF(Q3<'設定項目'!$B$7,Q3,'設定項目'!$B$7)

となりますね。

ただし、休日の場合は法定時間内だろうがなかろうが1.35で計算することになっていますので、この条件は営業日のみの適用となります。

ですから、計算式としては

=IF(T3>0,0,IF(Q3<'設定項目'!$B$7,Q3,'設定項目'!$B$7))

として、休日のときはゼロになるようにしてあげます。先ほど計算式を作成したT列を活用して、該当日が休日なのかどうかを判断しています。

時間外労働時間を算出

ではS列の残業時間を算出していきます。

労働時間のうち、時間内労働時間でも休日労働時間でもなければそれが時間外労働つまり残業時間ということになり、その時間は時間単価×1.25で計算をすることになります。

合計の労働時間はQ列に出ていますから、時間内労働時間R列と休日労働時間T列を減算すればOKです。例えばセルS3であれば

=Q3-R3-T3

となります。

というか、前回までで一生懸命L列とかO列を算出したんですがてんで使ってないですね…。まあ、出しておいたほうがわかりやすいので良しとしましょう。

深夜労働時間を算出

最後に深夜労働時間を計算します。

これまでの3項目、時間内労働、時間外労働、休日労働の和がその日の労働時間の合計になり、それぞれの時間単価を乗算してお給料が決まっていくのですが、深夜時間帯(22時から5時まで)の労働に関しては、それに追加して深夜労働時間×0.25に当たる金額を手当てとして支払う必要があります。

計算方法は既にK列とP列に算出されているので簡単です。例えばセルU3であれば

=K3+P3

となります。

まとめ

これでスプレッドシート上に時間内労働時間、残業時間(時間外労働時間)、休日労働時間、深夜労働時間を算出することができました。

ここまで来ればあと一歩!

あとは合計を算出して微調整をしていけばシートのほうはOKですね。

次回、お伝えしていきたいと思います。

Googleスプレッドシートによる勤怠管理シートをCOUNTIFS関数と書式設定で仕上げる
Googleスプレッドシートを使って勤怠管理集計システムを作成していくシリーズです。COUNTIFS関数などを駆使して出勤日数や欠勤日数など日数の合計および時間外や深夜などの労働時間の合計を求めます。

どうぞお楽しみに!

連載目次:Googleスプレッドシートで勤怠管理集計システムを作る

勤怠管理業務はたくさんのスタッフとのやり取りもありますし、集計もなかなか複雑です。本シリーズでは複数人や離れた場所での共同作業も得意なスプレッドシートを用いてクラウド上で勤怠管理システムを作る方法をお伝えしていきます。
  1. Googleスプレッドシートでクラウド上の勤怠管理集計システムを作る
  2. 【スプレッドシート初心者向け】定番関数と表示形式で手入力しないで日付と曜日を出力
  3. GoogleスプレッドシートのVLOOKUP関数で複数条件による検索を行う方法
  4. 【スプレッドシートで給与計算】打刻時間を指定の分単位で切り上げ、切り捨てをする方法
  5. GoogleスプレッドシートでIF文を超絶駆使して通常・残業・深夜の就業時間を求める
  6. Googleスプレッドシートで時間内・時間外(残業)・休日・深夜の労働時間を計算する
  7. Googleスプレッドシートによる勤怠管理シートをCOUNTIFS関数と書式設定で仕上げる

タイトルとURLをコピーしました