エクセルで勤怠管理表を作ろうとしています。 1日に最大2回の休憩があるので、出勤と退勤を3回入力出来るようにしまし…

Question

エクセルで勤怠管理表を作ろうとしています。

1日に最大2回の休憩があるので、出勤と退勤を3回入力出来るようにしました。この表に出勤時間と退勤時間を入力するだけで【普通時間】【深夜時間】【普通残業時間】【深夜残業時間】が出るようにするにはどうしたら良いでしょうか？

Answer 1

残業を普通と深夜に分ける必要はありません。深夜で25%加算残業で25%加算をそれぞれ適用すれば、深夜残業は自動的に重複して加算されます。

出勤1から退勤1までのうち時刻1から時刻2に該当する時間は「退勤1と時刻2の早い方から出勤1と時刻1の遅い方を引く、ただしマイナスにはしない」ということで
=max(0, min(退勤1*1,時刻2)-max(出勤1,時刻1))
8時から17時でも22時から29時でも数式の形は全く同じ。出勤退勤の1〜3それぞれ計算して合計するだけですね。

で、所定時間が8時間なら、残業は「実働時間の合計から8時間を引く、ただしマイナスにはしない」ので
=max(0, 実働時間の合計-"8:00")
となります。

Answer 2

深夜時間を何時から何時と定義するかによって関数は変わりますが、ここでは22時から翌5時を深夜時間と仮定して説明します。

H3セル（普通時間）：
=SUM(IF(C3>B3,C3-B3,0),IF(E3>D3,E3-D3,0),IF(G3>F3,G3-F3,0))
各出退勤の時間を比較し、時間が正であればその差を合計することで、合計の普通時間を算出します。

I3セル（深夜時間）：
=SUM(IF(AND(C3>=TIME(22,0,0),C3<=TIME(5,0,0)),MIN(TIME(5,0,0),C3)-MAX(TIME(22,0,0),B3),0),
IF(AND(E3>=TIME(22,0,0),E3<=TIME(5,0,0)),MIN(TIME(5,0,0),E3)-MAX(TIME(22,0,0),D3),0),
IF(AND(G3>=TIME(22,0,0),G3<=TIME(5,0,0)),MIN(TIME(5,0,0),G3)-MAX(TIME(22,0,0),F3),0))
各出退勤時間が22時以降5時以内の範囲にある場合、その範囲内の時間を計算し、合計することで、合計の深夜時間を算出します。

J3セル（普通残業時間）：
=MAX(0,H3-TIME(8,0,0))
普通時間が8時間を超えた場合、超過分を普通残業時間として算出します。

K3セル（深夜残業時間）：
=I3
深夜時間はそのまま深夜残業時間とします。