Excelの関数、計算式について質問です。 勤務時間計算において 勤務時間 5:00～22:00の勤務時間 深…

Question

Excelの関数、計算式について質問です。

勤務時間計算において
勤務時間 5:00～22:00の勤務時間
深夜時間 23:00～5:00の勤務時間
超過時間 8時間以上超過深夜時間 8時間以上勤務した場合の深夜時間
＊8時間以上の深夜時間は割増のため。8時間未満の勤務の場合は０分

超深夜時間の計算式ができません。

B2に勤務時間、C２に退勤時間、D３に休憩時間(時間入力)、E３に勤務時間、F２に深夜時間、G2に超過時間、H２に超過深夜時間

現在、知恵袋参考に下記まで設定しています。

E3に
=IF(C3="","",IF(COUNT(B3:C3)<>2,"",MAX(0,MIN("22:00",C3)-MAX(B3,"5:00")))-D3)

F3に
=IF(B3="","","5:00"-MIN("5:00",B3)+MIN("29:00",C3)-MIN(MAX("22:00",B3),C3))

G３に
=IF(C3="","",IF(C3-B3-D3>=TIME(8,0,0),C3-B3-D3-TIME(8,0,0),0))

H３ができません。
=IF(G3>0,F3)で入ロしましたが、深夜時間より超過時間が少ない場合、深夜時間がそのまま反映されてしまいます。重複時間した時間のみを表示したいです。

よろしくお願いします。

Answer 1

C3="","",IF(COUNT(B3:C3)<>2,"", の部分は冗長で邪魔なのでいりません。

8時間を超過した部分は深夜でも昼間でも割り増し、深夜帯は8時間を超過しなくても割り増しなので、重複した部分の割り増しが特に手厚いってことがなければわざわざ別に計算する必要はないんですが、たとえば8時間を超えたところは「出勤時刻+休憩時間+"8:00"から退勤時刻まで」なので、それが深夜帯に該当するのは
=max(0, min(退勤時刻*1, "29:00")-max(出勤時刻+休憩時間+"8:00", "22:00"))
ですね。