休憩時間が数パターンある場合の休憩時間の計算

勤務表を作成していますが、
休憩時間が複数ある場合の休憩時間の算出方法が知りたいです。

C3 9:00 (出社時刻)
D3 20:00 (退社時刻)
E3 (休憩時間)
F3 =D3-C3-E3 (その日の稼働時間)

C4 13:00 (出社時刻)
D4 18:00 (退社時刻)
E4 (休憩時間)
F4 =D4-C4-E4 (その日の稼働時間)

休憩時間1
C40 12:00 (休憩開始時刻)
D40 13:00 (休憩終了時刻)
E40 1:00 (休憩時間の合計)


休憩時間2
C41 18:00 (休憩開始時刻)
D41 18:30 (休憩終了時刻)
E41 0:30 (休憩時間の合計)

休憩時間自体は6パターンあります。

休憩時刻が変わった際にも対応できるよう
E40やE41を使って、退社時刻によって
休憩時間を算出できるようにしたいです。

また、13時に出社した時に12:00-13:00の休憩時間を
カウントしないようにもしたいです。

Excel 関数のアドバイスを
よろしくお願い致します。

No.2 回答者： bunjii 回答日時： 2014/04/25 14:28

http://oshiete.goo.ne.jp/qa/8566549.html の再質問でしょうか？
上記の回答No.2で一部不備がありましたので修正して提言します。

>休憩時刻が変わった際にも対応できるようE40やE41を使って、退社時刻によって休憩時間を算出できるようにしたいです。
前回の質問へ回答したときも指摘しましたが、E40とE41を利用するには出社時刻または退社時刻が休憩時間帯にならないことが条件になります。
下記の数式では出社、退社の時刻が休憩時間帯でも正しく算出できます。
E3=IF(COUNT(C3:D3)=2,SUM(MIN(MAX(D3,$C$40),$D$40)-MAX(MIN(C3,$D$40),$C$40),MIN(MAX(D3,$C$41),$D$41)-MAX(MIN(C3,$D$41),$C$41)),"")

貼付画像はExcel 2013で検証した結果ですが全てのバージョンで再現できるはずです。

>休憩時間自体は6パターンあります。
今回提案のSUM関数は範囲の追加で6パターン以上でも対応できます。

No.1 回答者： aokii 回答日時： 2014/04/24 17:09

https://sites.google.com/site/kinnmujikannnokeis …