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

photo credit: Timeless via photopin (license)

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

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

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

【スプレッドシートで給与計算】打刻時間を指定の分単位で切り上げ、切り捨てをする方法

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

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

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

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

通常、残業、深夜時間帯の設定
就業時間を算出するスプレッドシート計算式
休憩時間を除き合計を求める
まとめ
連載目次：Googleスプレッドシートで勤怠管理集計システムを作る

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

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

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

00:00～07:00：深夜時間帯（朝）
07:00～09:00：残業時間帯（朝）
09:00～18:00：通常時間帯
18:00～23:00：残業時間帯（夜）
23:00～24:00：深夜時間帯（夜）

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

例えば、開始時刻が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列の５つの時間帯でそれぞれ計算式が異なってきますので、それぞれについて地道に考えていきます。

深夜時間帯（朝）の就業時間

まず、一番考えやすい、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」ですが、この場合は２パターンありまして、開始時刻が9:00以降だった場合と終了時刻が7:00以前だった場合は、残業時間帯（朝）の就業時間はゼロとなります。

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

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

開始が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)))

となります。