Excelの関数で、残業時間計算表をつくりたいです。

Question

Excelの関数で、残業時間計算表をつくりたいです。退勤時間の箇所に退勤時間を入力すると、残業時間(標準)と残業時間(切り捨て)が計算できる表をつくりました。17：00～17：10は休憩時間とみなされているため、10分引いた時間が残業時間にしています。
(標準)は10分引いたそのままの時間が計算されて、(切り捨て)は5分単位で4分にタイムカードを打刻すると0分で、9分にタイムカードを打刻すると5分に計算されます。
この休憩時間17：00～17：10にくわえて19：00～19：10も休憩時間として計算できるようにしたいです。

D3には、=IF(B3="合計",MAX(0,SUM(C$3:C6)-COUNT(C$3:C3)*"17:10"),IF(C3<="17:00"*1,"",C3-"17:10"))
E3には、=IF(C3="","",INT(SUM(C3)*288-COUNT(C3)*206+10^-5)/288 )

という関数を現在いれています。
よろしくお願いします。

Answer 1

画像参照ください。
全残業時間から、退勤時刻が19:10を過ぎていなければ休憩時間を0:10、過ぎていれ0:20 引くという感じの式です。

C2 =IF(B2="","",IF(B2>"19:10"*1,B2-"17:20"*1,B2-"17:10"*1)) 下にコピー
合計 C11 =SUM(C2:C10)

D2 =FLOOR(C2,"0:05") 下にコピー
合計 D11 =SUM(D2:D10)

現在はB列が"合計"かどうかで、行毎に計算式を変えている様ですが
合計は最終行でSUM()で簡単に計算できますので、行毎の式は簡素化した方がいいと思い、それを含めての回答です。

Answer 2

添付参照
Ｃ4に =FLOOR(B4,"0:05") これで 退勤時刻を ５分単位で 切り捨てします。
Ｄ列は 通常勤務時間の計算用です。今回は 省略
E1:F2に 休憩を除いた勤務時間を 入れます
E1:E2 17:10～19：00
F1:F2 19:00～30:00(翌日６時）
E4に =IF($C4="","",MAX(0,MIN($C4,E$2)-MAX("17:00"*1,E$1)))
これで E1(17:10) 以降 E2(19:00)の間の勤務時間がでます
これを Ｆ4に オートフィルすれば
=IF($C4="","",MAX(0,MIN($C4,F$2)-MAX("17:00"*1,F$1))) となり
F1(17:10) 以降 F2(19:00)の間の勤務時間がでます
従って Ｇ４に =IF(C4="","",SUM(E4:F4)) で 合計が出ます。
後は C4:G4を 下にオートフィル

同じやり方で 通常勤務の時間も 同様に計算できます。

E:F列は 非表示でも 構いません