GoogleスプレッドシートでIF文を超絶駆使して通常・残業・深夜の就業時間を求める

★気に入ったらシェアをお願いします!

working-time

photo credit: Timeless via photopin (license)

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

Googleスプレッドシートを使った勤怠管理集計システムを引き続き作っています。

前回はこちらの記事でした。

【スプレッドシートで給与計算】打刻時間を指定の分単位で切り上げ、切り捨てをする方法
Googleスプレッドシートを使った勤怠管理集計システムを作っています。今回は、CEILING関数、FLOOR関数そしてTIME関数を活用して時刻を指定の分単位で切り上げ、切り捨てをする方法です。

CEILING関数、FLOOR関数を使って、打刻時間を特定の時間単位で丸める方法についてお伝えしました。

これで、開始時刻、終了時刻が算出できましたので、今回はいよいよそれらのデータを使って通常時間、残業時間、深夜時間のそれぞれの就業時間を算出していきたいと思います。

では、行ってみましょう!

通常、残業、深夜時間帯の設定

就業時間のうち、どこまでを残業時間としてまた深夜時間としてカウントするかについては各企業で異なることと思います。

今回の例としては以下の「設定項目」シートで指定をしている通り

スプレッドシートの勤怠管理の設定シート

  • 00:00~07:00:深夜時間帯(朝)
  • 07:00~09:00:残業時間帯(朝)
  • 09:00~18:00:通常時間帯
  • 18:00~23:00:残業時間帯(夜)
  • 23:00~24:00:深夜時間帯(夜)

という5つの時間帯に分類して、それぞれの時間帯での就業時間を算出していく計算式を見出していきます。

例えば、開始時刻が08:00で、就業時刻が20:00だったとすると、それぞれの時間帯内の就業時間は

  • 00:00~07:00:深夜時間帯(朝)→0:00
  • 07:00~09:00:残業時間帯(朝)→1:00
  • 09:00~18:00:通常時間帯   →9:00
  • 18:00~23:00:残業時間帯(夜)→2:00
  • 23:00~24:00:深夜時間帯(夜)→0:00

となります。

もう一つの例として、開始時刻が13:00で、就業時刻が24:00だったとすると、それぞれの時間帯内の就業時間は

  • 00:00~07:00:深夜時間帯(朝)→0:00
  • 07:00~09:00:残業時間帯(朝)→0:00
  • 09:00~18:00:通常時間帯   →5:00
  • 18:00~23:00:残業時間帯(夜)→5:00
  • 23:00~24:00:深夜時間帯(夜)→1:00

と算出されるようにしたいわけです。

日本の労働時間の計算…なかなか大変ですね。

就業時間を算出するスプレッドシート計算式

まず現状の「勤怠管理」シートは以下の状態です。

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

開始時刻I列、終了時刻J列をもとに、K~P列をそれぞれ算出していくことになります。(N列は今回は無視して、次回以降対応をします。)

K~P列の5つの時間帯でそれぞれ計算式が異なってきますので、それぞれについて地道に考えていきます。

深夜時間帯(朝)の就業時間

まず、一番考えやすい、00:00~07:00の深夜時間帯(朝)について考えていきます。

開始時刻、終了時刻に対して、算出される深夜時間帯(朝)の就業時間について、このようなマトリクスを作ってみました。

深夜時間帯(朝)の就業時間を求めるマトリクス

例えば、開始時刻が05:00、終了時刻が6:00だった場合は

  • 開始時刻:0:00~7:00
  • 終了時刻:0:00~7:00

の交点である、「終了-開始」が計算式として採用される形になります。これはそれぞれ、終了時刻と開始時刻なので、算出される深夜時間帯(朝)の就業時間は

6:00-5:00=1:00

となります。

もう一つ例を出しておきますと、開始時刻が6:00、終了時刻が18:00だった場合は

  • 開始時刻:0:00~7:00
  • 終了時刻:9:00~18:00

の交点である、「7:00-開始」が深夜時間帯(朝)の就業時間となります。この場合の計算としては、開始時刻は6:00ですから

7:00-6:00=1:00

が深夜時間帯(朝)の就業時間となります。

黒で塗られている箇所は、開始時刻よりも終了時刻のほうが早い時刻になるので「存在し得ないパターン」です。ここについては考慮をしません。

グレーで「0」とある箇所はその時間帯の就業時間は文字通り「0」となる範囲です。というのも開始時刻が7:00より後ですから、深夜残業帯(朝)の就業時間は当然ながらゼロとなりますよね。

これをスプレッドシートの計算式で表現する場合はIF文を多数駆使していくことになります。まず、わかりやすさのために、日本語で表現をしてみますと

  • 開始が7:00以上→0
  • さもなくば
    • 終了が7:00より小さい→終了-開始
    • さもなくば→7:00-開始

となります。これをスプレッドシートの計算式で表現をしますと

=IF(開始>=7:00,0,IF(終了<7:00,終了-開始,7:00-開始))

となりますが、例えば3行目のK3セルを求めるとすると、ここでの「開始」「終了」「7:00」はそれぞれ

  • 開始:I3
  • 終了:J3
  • 7:00:’設定項目’!$B$1

ですから、最終的には

=IF(I3>=’設定項目’!$B$1,0,IF(J3<‘設定項目’!$B$1,J3-I3,’設定項目’!$B$1-I3))

となります。

残業時間帯(朝)の就業時間

同じように残業時間帯(朝)の就業時間の計算式を求めていきたいと思いますが、少し難易度が上がります。

開始時刻、終了時刻に対するマトリクスとしてはこのようになります。

残業時間帯(朝)の就業時間を求めるマトリクス

黒の塗りつぶしの箇所は先ほどのパターンと同様で「存在しない」ケースです。

グレーの「0」ですが、この場合は2パターンありまして、開始時刻が9:00以降だった場合と終了時刻が7:00以前だった場合は、残業時間帯(朝)の就業時間はゼロとなります。

実際に値が算出されるパターンはこちらの図のように4パターンに分かれます。

残業時間帯(朝)の就業時間を求めるマトリクスでパターン分け

以上をまず日本語で表現をしてみますと

  • 開始が9:00以上または終了が7:00より小さい→0
  • さもなくば
    • 終了が9:00より小さい
      • 開始が7:00より小さい→終了-7:00
      • さもなくば→終了-開始
    • さもなくば
      • 開始が7:00より小さい→9:00-7:00
      • さもなくば→9:00-開始

となります。

IF文に置き換えてみますと

=IF(OR(開始>=9:00,終了<7:00),0,
IF(終了<9:00,
IF(開始<7:00,終了-7:00,終了-開始),
IF(開始<7:00,9:00-7:00,9:00-開始)))

例えば3行目のL3セルを求めるとすると、ここでの「開始」「終了」「7:00」「9:00」はそれぞれ

  • 開始:I3
  • 終了:J3
  • 7:00:’設定項目’!$B$1
  • 9:00:’設定項目’!$B$2

ですから、置き換えてみますと

=IF(OR(I3>=’設定項目’!$B$2,J3<‘設定項目’!$B$1),0,
IF(J3<‘設定項目’!$B$2,
IF(I3<‘設定項目’!$B$1,J3-‘設定項目’!$B$1,J3-I3),
IF(I3<‘設定項目’!$B$1,’設定項目’!$B$2-‘設定項目’!$B$1,’設定項目’!$B$2-I3)))

なかなか大変ですね…

通常時間の就業時間

じゃんじゃん行きましょう。次は通常時間帯です。

マトリクスは

通常時間帯の就業時間を求めるマトリクス

です。

通常時間帯の就業時間帯の算出方法を日本語で表現すると

  • 開始が18:00以上または終了が9:00より小さい→0
  • さもなくば
    • 終了が18:00より小さい
      • 開始が9:00より小さい→終了-9:00
      • さもなくば→終了-開始
    • さもなくば
      • 開始が9:00より小さい→18:00-9:00
      • さもなくば→18:00-開始

となります。

IF文への置き換えをすると

=IF(OR(開始>=18:00,終了<9:00),0,
IF(終了<18:00,
IF(開始<9:00,終了-9:00,終了-開始),
IF(開始<9:00,18:00-9:00,18:00-開始)))

例えばM3を求めるとすると、セルの参照としては以下の通りになりますから

  • 開始:I3
  • 終了:J3
  • 9:00:’設定項目’!$B$2
  • 18:00:’設定項目’!$B$3

最終的な計算式は

=IF(OR(I3>=’設定項目’!$B$3,J3<‘設定項目’!$B$2),0,
IF(J3<‘設定項目’!$B$3,
IF(I3<‘設定項目’!$B$2,J3-‘設定項目’!$B$2,J3-I3),
IF(I3<‘設定項目’!$B$2,’設定項目’!$B$3-‘設定項目’!$B$2,’設定項目’!$B$3-I3)))

となります。

残業時間帯(夜)の就業時間

ようやく折り返しですね。残業時間帯(夜)について進めていきましょう。

マトリクスは

残業時間帯(夜)の就業時間を求めるマトリクス

です。

通常時間帯の就業時間帯の算出方法を日本語で表現すると

  • 開始が23:00以上または終了が18:00より小さい→0
  • さもなくば
    • 終了が23:00より小さい
      • 開始が18:00より小さい→終了-18:00
      • さもなくば→終了-開始
    • さもなくば
      • 開始が18:00より小さい→23:00-18:00
      • さもなくば→23:00-開始

となります。

なんとなくパターンが掴めてきたころと思います。

IF文への置き換えをすると

=IF(OR(開始>=23:00,終了<18:00),0,
IF(終了<23:00,
IF(開始<18:00,終了-18:00,終了-開始),
IF(開始<18:00,23:00-18:00,23:00-開始)))

O3セルを求める場合のセルの参照を以下で置き換えると

  • 開始:I3
  • 終了:J3
  • 18:00:’設定項目’!$B$3
  • 23:00:’設定項目’!$B$4

最終的なIF文は

=IF(OR(I3>=’設定項目’!$B$4,J3<‘設定項目’!$B$3),0,
IF(J3<‘設定項目’!$B$4,
IF(I3<‘設定項目’!$B$3,J3-‘設定項目’!$B$3,J3-I3),
IF(I3<‘設定項目’!$B$3,’設定項目’!$B$4-‘設定項目’!$B$3,’設定項目’!$B$4-I3)))

となります。

深夜時間帯(夜)の就業時間

さあいよいよ最後です。深夜時間帯(夜)の就業時間を算出していきます。

マトリクスはこちら。

深夜時間帯(夜)の就業時間を求めるマトリクス

お、簡単そうですね。

日本語表現としては

  • 終了が23:00より小さい→0
  • さもなくば
    • 開始が23:00より小さい→終了-23:00
    • さもなくば→終了-開始

となりますね。

IF文に置き換えると

=IF(終了<23:00,0,IF(開始<23:00,終了-23:00,終了-開始))

となります。セル参照を正しく表現すると

=IF(J3<‘設定項目’!$B$4,0,IF(I3<‘設定項目’!$B$4,J3-‘設定項目’!$B$4,J3-I3))

となります。

休憩時間を除き合計を求める

あと少し進めてしまいましょう。

まずN列に休憩時間を除く通常時間帯の就業時間を算出します。

ルールとしては

  • 通常時間帯の就業時間が、休憩時間以上であれば休憩時間を減算する
  • 通常時間帯の就業時間が、休憩時間に満たなければ0とする

というルールにしてみようと思います。

各社で計算のルールは異なると思いますので、ルールに則って計算式を調整してくださいね。

例えばセルN3であれば

=IF(M3<='設定項目'!$B$5,0,M3-'設定項目'!$B$5)

となります。

続いて、Q列に就業時間帯の合計を求めます。

例えばセルQ3であれば

=SUM(K3:P3)-M3

となります。

いずれも31日分、33行目までコピーすればOKですね。

ようやく終わりました…!

まとめ

IF文の嵐でしたが、このようなシートが作れるようになると、かなりのスプレッドシートマスターになれたと言っていいのではないでしょうか。

人事の担当者さん…大変ですよね。

さて、それぞれの数式を31日分までコピーしましてこのようになりました。

スプレッドシートで就業時間を計算

まだまだ大変な作業が続きますが、次回は時間内、残業、休日、深夜などの各労働時間の計算を進めていきます。

Googleスプレッドシートで時間内・時間外(残業)・休日・深夜の労働時間を計算する
Googleスプレッドシートを使って勤怠管理集計システムを作成しています。今回は最大の難関ともいえる各労働時間つまり時間内労働、時間外労働(残業)、休日労働、深夜労働の時間を計算していきます。

どうぞお楽しみに!

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

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