通常労働時間、残業時間、深夜時間、深夜残業時間、算出方法について 出勤時間、退勤時間、休憩時間(22：00以前取得…

Question

通常労働時間、残業時間、深夜時間、深夜残業時間、算出方法について

出勤時間、退勤時間、休憩時間(22：00以前取得)、休憩時間(22：00～29：00取得)を入力すると下記の時間が算出されるようにエクセルで計算式を作成したいのですが、詳しい方教えて頂きたいです。

・通常労働時間＝8時間を上限
・残業時間＝8時間を上回った部分で深夜以外の残業時間
・深夜時間＝8時間以内の深夜分
・深夜残業時間＝8時間以上の深夜分

例 出勤8：00 退勤18：00
休憩1：00
＝労働時間8：00時間 残業時間1：00時間

例 出勤17：00 退勤25：00
休憩1：00（22：00以降に休憩取得）
＝労働時間5時間 深夜時間2時間

例 出勤13：00 退勤25：00
休憩1：00(22：00以前取得）休憩1：00(22：00以降取得)
＝労働時間8：00 深夜残業時間2：00時間

例 出勤8：00 退勤12：00
休憩なし
＝労働時間4：00時間

宜しくお願い致します。

Answer 1

添付画像の様に入力したとして。
時刻、時間数は全てシリアル値での入力が前提です。

F2 セルの数式： =ROUND(C2-B2,10)
G2 セルの数式： =ROUND(MIN(C2-B2-D2,$L$2),10)
H2 セルの数式： =ROUND(MAX(0,F2-G2),10)
I2 セルの数式： =ROUND(MAX(0,C3-$M$2)-E3,10)
J2 セルの数式： =ROUND(MIN(F2-G2,I2),10)

時刻、時間数の計算は、演算誤差が生じ易いので、対策として「ROUND」関数を使っています。

不慣れな為かもしれませんが、ご質問には
◆ Excel のバージョン
◆ 要求仕様の概要だけでなく、詳細（セルアドレス、各データ型など）
※ 可能な限りスクリーンショットが望ましい

これらの記載をお願いします。

Answer 2

以下が通常労働時間、残業時間、深夜時間、深夜残業時間の算出式です。各時間は数値型のセルに入力してください。
通常労働時間：=IF((C2-B2)*24-D2>8, 8, (C2-B2)*24-D2)
残業時間：=IF((C2-B2)*24-D2>8, IF((C2-B2)*24-D2-8>=TIME(0,0,0), 8, (C2-B2)*24-D2-8), 0)
深夜時間：=IF(C2>TIME(22,0,0), IF(B2>TIME(22,0,0), 0, (C2-TIME(22,0,0))*24), IF(B2<TIME(5,0,0), (C2-B2)*24, IF(B2>TIME(22,0,0), (TIME(29,0,0)-B2)*24, 0)))
深夜残業時間：=IF(C2>TIME(22,0,0), IF(B2>TIME(22,0,0), IF((C2-B2)*24-D2>8, (C2-B2)*24-D2-8, 0), IF((C2-TIME(22,0,0))*24-D2>8, IF((C2-TIME(22,0,0))*24-D2-8>=TIME(0,0,0), 8, (C2-TIME(22,0,0))*24-D2-8), 0)), 0)
上記式では、出勤時間をB2、退勤時間をC2、22時以前の休憩時間をD2、22時から29時までの休憩時間をE2としています。また、深夜時間の定義は、22時以降から5時までの時間帯で、深夜残業時間の定義は、8時間以内の深夜時間を超えた時間帯です。