エクセル 深夜休憩時間(深夜労働時間）を求めたい

エクセルで深夜労働時間と深夜休憩時間を求めています。
深夜労働時間は午後10時から午前5時のなので、深夜休憩時間も午後10時から午前5時で求めています。


次のような表を作成し、深夜休憩時間と、普通休憩時間をもとめるにはどのような関数にすればよいのでしょうか。
特に、①や③のように深夜労働時間をまたぐような関数がわからないのでアドバイスをお願いいたします。

休憩時間　　　　　　　普通休憩時間　　　　　　深夜休憩時間
①21：30～22：30　　　　　　30分　　　　　　　　　　　30分
②22：30～23：30　　　　　　　　　　　　　　　　　　　 60分
③04：30～05：15　　　　　　15分　　　　　　　　　　　30分


ここでいう普通休憩時間とは午前10時から午前5時以外の休憩時間としています。

質問者からの補足コメント

• 上追記します。
  休憩時間の計算だけでしたら、
  ①だと、　2015/08/22/　22：30：00　－　2015/08/22　21：30：00　なのですが、
  それを、　深夜休憩時間　の30分、それ以外（22時から5時以外）の30分にわけて、表示や計算が
  できたらなと考えています。
  
  たとえば　普通休憩時間の列には　22時から5時以外の休憩時間を表示し、
  深夜休憩時間の列には　22時から5時の休憩時間を表示したいです。
  
  if　で細かく条件分岐する方法しかわからなかったので、こちらで質問してみました

    補足日時：2015/08/22 10:55

No.3 回答者： shorun 回答日時： 2015/08/22 13:46

No2です、

ごめんなさい、4:30~5:15部分が未検証のまま回答してしまって間違っていました。

この回答へのお礼

わざわざ試してくださりありがとうございます。
やはり、if分で条件分岐するのですね。
それがわかっただけでもありがたいです。

ありがとうございました。

お礼日時：2015/08/22 14:52

No.2 回答者： shorun 回答日時： 2015/08/22 13:37

添付図の様式で計算するなら次の式を各列の最終行までコピー貼り付ける。

セルC1の式 =IF(A3>=$D$1,0,IF(A3>=$C$1,$D$1-A3,$C$1-A3))
セルD1の式 =IF(B3<=$D$1,0,IF(A3<=$D$1,$D$1-A3,B3-$D$1))

No.1 回答者： 銀鱗 回答日時： 2015/08/22 09:42

関数は関係ない。

普通に引き算すればよいが、日付を含めた計算をする必要があるだけだ。
Excelの時間は日付と一緒になっているシリアル値として扱われる。
1900年1月1日0時0分0秒を基点に、1日で１カウントする値になっている。（1時間は0.04166666666）
なので
（8月22日5時0分）－（8月21日22時0分）
の計算が可能。
セルの書式設定で日付を除いた時間表示にすると見やすくはなるが勘違いしやすいので注意。

この回答へのお礼

教えていただきありがとうございます。

上に例の普通休憩時間の列と、深夜休憩時間の列に分けて表示するにはどうすればよいのかと思っていました。

if　で　細かく条件判定して、引き算する方法したおもいつかなかったので。

ほかに方法はないでしょうか

お礼日時：2015/08/22 11:07