EXCELの関数で、就業時間帯から通常時間帯と深夜時間帯(22:00～05：00)を出したい場合について教えてください。…

Question

EXCELの関数で、就業時間帯から通常時間帯と深夜時間帯(22:00～05：00)を出したい場合について教えてください。
他の質問の回答を試してはいるのですが、なかなかうまくいきません。出勤時刻 退勤時刻 休憩開始 休憩終了 就労時間 通常就業 深夜就業 休憩時間
17：00 2：00 21：00 22:00

出退勤時刻を入力すると、就労時間に「8：00」、通常就業に「1：00」、深夜時間に「7：00」、休憩時間に「1：00」と表示させることは可能ですか？

お願い致します。

Answer 1

日付をまたいで24時以降の入力はセルの書式を [h]:mm にして25:00のようにすると式が簡潔になります。
それが出来ないと仮定して、作業列を使います。
図でJ列はセルの書式をユーザー定義で [h]:mm にします。
J2は
=IF(B2<C2,C2,"24:00"+C2)
下へオートフィル

F2
=J2-B2
G2
=IF(J2<="22:00"*1,F2,MIN(J2,"22:00")-B2)
H2
=IF(J2<="22:00"*1,0,J2-MAX(B2,"22:00"))
I2
=IF(D2<E2,E2-D2,E2+"24:00"-D2)
下へオートフィル

就労時間 通常就業 深夜就業は休憩分を引いていません。
これを引くとなると、休憩時間を考慮してさらに複雑な式になります。