エクセルで夜間の残業の時間を計算したいと考えています。 関数を組んで計算したいのですが、うまくいきません。 お知恵を…

Question

エクセルで夜間の残業の時間を計算したいと考えています。
関数を組んで計算したいのですが、うまくいきません。
お知恵をお借りできないでしょうか。
以下の勤務パターンを想定しています。①10:00-19:00 （17:00以降を残業時間として算出）
②10:00-23:30 （22:00までの残業と22:00以降の深夜の残業を分けて算出）
③10:00-2:00 （22:00までの残業と22:00以降の深夜の残業を分けて算出）
④22:00-23:30 （22:00～23:30を深夜残業時間として算出）
⑤22:00-2:00 （22:00～2:00を深夜残業時間として算出）
⑥1:00-5:00 （1:00～5:00を深夜残業時間として算出）

（条件）
・休憩は考慮しない。
・勤務時間において、24時以降は1:00、2:00と表示したい（25:00、26:00とはしな
い）
・①～③は17時以降は残業と扱いとし、④～⑥は勤務時間が短いがいずれも深夜残業時
間として扱う。

C列、D列にどのような関数を組むと算定可能でしょうか。

Answer 1

その書き方では、所定時間が何時からか解りません。
標準の会社に合わせて、8:00からとします。
･ 8:00～17:00 所定時間
･17:00～22:00 残業時間
･22:00～ 8:00 深夜時間

C2: =IF(B2="","",MAX(MIN(B2+(A2>B2),"22:0")-MAX(A2,"17:0"),0))
D2: =B2+(A2>B2)-A2-MAX(MIN(B2+(A2>B2),"22:0")-MAX(A2,"8:0"),0)
表示形式、ユーザー定義｢h:mm;;｣
下へコピペ。