深夜労働時間の集計 エクセル

エクセルで深夜労働時間の集計を行いたいのですが、計算式(関数)がいまいち不明です。ご教授願います。

　　A列 　　B列 　　C列 　　D列
　　出勤　　　　退勤　　　拘束時間　　　　深夜労働時間
１　4：00　　　22：30　　　18：30　　　　1：30(自動計算)

上記のような表が有ります。
尚、深夜労働時間は労基法に有る22：00～5：00の間で考えています。
よろしくお願いいたします。

No.1 回答者： hisajiro 回答日時： 2010/04/05 15:17

D1に深夜労働時間ですね？

=MIN(IF(A1>B1,"29:00",B1),B1+"24:00")-MAX("22:00",A1)

でどうでしょうか？
A1に出勤時間、B1に退出時間、C1に拘束時間とした場合です

この回答への補足

ありがとうございます。
確かに22：00～22：30で深夜時間は0：30ですが
勤務開始時点の4：00～5：00までの1：00が計算出来ません。
この場合どのような計算になるのでしょう・・・？

補足日時：2010/04/05 15:37

No.2 回答者： hisajiro 回答日時： 2010/04/05 15:54

では・・・

=MIN(IF(A1>B1,"29:00",B1),B1+"24:00")-MAX("22:00",A1)

でいかがでしょうか？
もし時刻ではなく小数点付きの数値になった場合はセルの書式設定の時刻でYY:MMを指定すれば出来ると思います。

お試しください。

この回答への補足

すみません、何度か試しましたが同じ結果(0：30)と計算されてしまいます。
退勤時間が22：30なので22：00～22：30までの深夜労働0：30と
出勤時間が4：00なので4：00～5：00までの深夜労働時間1：00を
計算させたいと考えています。

補足日時：2010/04/05 16:17

No.3 回答者： hisajiro 回答日時： 2010/04/05 16:29

ごめんなさい。

同じ関数書いてまして(^_^;)
正しくは
MAX(MIN(B1,"5:00"+(A1<B1))-MAX(A1,"22:00")+(A1>B1),0)+IF(A1<"5:00"*1,"5:00"-A1,0)
です。

失礼致しました。ｍ（＿＿）ｍ

この回答へのお礼

大変ありがとうございます。
おかげさまでバッチリ思い通りの表が作成できました。
最後に、もう一つだけ質問させて下さい。

お答え頂いた式を入力した所、休みの日で出勤の時間と退勤の時間が入力されていない場合に、5：00と表示されてしまいます。
休みの日は空白セルにしたいのですが、回避する方法は有りますか？

お礼日時：2010/04/05 16:43

No.4 回答者： hisajiro 回答日時： 2010/04/05 16:50

これでできませんか？

IF(A4<>"",MAX(MIN(B4,"5:00"+(A4<B4))-MAX(A4,"22:00")+(A4>B4),0)+IF(A4<"5:00"*1,"5:00"-A4,0),"")

No.5 回答者： hisajiro 回答日時： 2010/04/05 16:51

訂正ですｍ（＿＿）ｍ

IF(A1<>"",MAX(MIN(B1,"5:00"+(A1<B1))-MAX(A1,"22:00")+(A1>B1),0)+IF(A1<"5:00"*1,"5:00"-A1,0),"")

この回答へのお礼

早い回答ありがとうございます。
完璧な表になりました!!
これで月の計算がかなり短縮されます～
本当に助かりました。
ありがとうございました。

お礼日時：2010/04/05 17:02

No.6 回答者： KURUMITO 回答日時： 2010/04/05 17:28

Ｄ1セルには次の式を入力し、下方にオートフィルドラッグします。

=IF(COUNT(A1:B1)<>2,"",IF(B1>=A1,MIN(B1,"29:00")-MAX(A1,"22:00"),MIN(B1+"24:00","29:00")-MAX(A1,"22:00"))+IF(A1<"5:00"*1,"5:00"-A1,0))

No.7 回答者： KURUMITO 回答日時： 2010/04/05 17:34

先の式ではＡ1セルが4:00でＢ1セルが3：00のような場合には誤った計算となりますのでＤ1セルへの入力の式は次のようにしてください。

=IF(COUNT(A1:B1)<>2,"",IF(B1>=A1,MIN(B1,"29:00")-MAX(A1,"22:00"),MIN(B1+"24:00","29:00")-MAX(A1,"22:00"))+IF(AND(A1<"5:00"*1,B1>"5:00"*1),"5:00"-A1,0))