エクセル 就業時間計算について

質問

エクセルで勤務時間計算をしたいと思っています。
ですが、どうしてもうまく計算ができません・・・
どなたかご存知の方、お力をかしてください<m(__)m>

例）
　　　　　 A　　　　　　　B 　　　　　 　C 　　　　　　D　　 　　　 E
　　　出社時間　　退社時間　　通常残業　 　深夜　 合計(勤務合計）
　１　　22：00　　　　 10：00
　２ 　　 4：00　　　　 14：00
　３　　23：00　　　　 15：:00

勤務時間が不規則なので、出勤時間まちまちです・・・
それと、通常残業と、深夜勤務の時間をふりわけが出来れば尚助かります。

どうか　C D E　に入れる関数を教えてください

回答No.2

KURUMITO · ベストアンサー

1行目は項目名が有るとして通常残業のC2セルには次の式を入力して下方にドラッグコピーします。

=IF(COUNT(A2:B2)<>2,"",IF(A2<B2,IF(B2-A2>="9:00"*1,B2-A2-"1:00"-"8:00",0),IF(B2+"24:00"-A2>="9:00"*1,B2+"24:00"-A2-"1:00"-"8:00",0)))

深夜のD2セルには次の式を入力して下方にドラッグコピーします。

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

合計のE2セルには次の式を入力して下方にドラッグコピーします。

=IF(COUNT(A2:B2)<>2,"",IF(A2<B2,IF(B2-A2>="8:00"*1,B2-A2-"1:00",B2-A2),IF(B2+"24:00"-A2>="8:00"*1,B2+"24:00"-A2-"1:00",B2+"24:00"-A2)))

各セルにはシリアル値が表示されますのでセルの表示形式の「時刻」から望みの形式を選択します。

補足

KURUMITOさん
ありがとうございます。とても親切に教えて頂き本当に感謝しております。
ただ今、KURUMITOさんに教えて頂いた式をもとに作成中ですが、もう一点わからない計算がありまして
とても厚かましいお願いですが教えて頂ければ幸いです。

16時間超過勤務計算式をお願いできませんでしょうか・・・

回答No.3

KURUMITO

16時間以上の超過勤務については24時間以上の勤務時間となり、今回の方法では対応できません。時間の入力を翌日については30:00といった入力にしないことには解決できませんね。
翌日の時間の入力を6:00のように入力して対応できる式ということで対応してきました。
翌日の時間を30:00のように入力するのでしたらC2セルには次の式を入力して下方にドラッグコピーします。

=IF(COUNT(A2:B2)<>2,"",IF(B2-A2>="9:00"*2,B2-A2-"2:00"-"8:00",IF(B2-A2>="9:00"*1,B2-A2-"1:00"-"8:00",0)))

D2セルには次の式を入力して下方にドラッグコピーします。

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

E2セルには次の式を入力して下方にドラッグコピーします。

=IF(COUNT(A2:B2)<>2,"",IF(B2-A2>="9:00"*2,B2-A2-"2:00",IF(B2-A2>="9:00"*1,B2-A2-"1:00",B2-A2)))

なお、B列でのセルの表示形式はユーザー設定から [h]:mm とします。

お礼

KURUMITOさん
親切に教えて頂き、本当にありがとうございました<m(__)m>
本当に助かりました。心からお礼申し上げます。

回答No.1

yambejp

１，２，３それぞれの通常残業、深夜はどういう計算根拠で
どう表記される想定でしょうか？

補足

A　　　　 　B 　　　　　C 　　　　D　　 　　　 E
　　　出社時間　　退社時間　　通常残業　 　深夜　　 合計(勤務合計）
　１　　22：00　　　　10：00 　　 　　 3:00 　　　7：00 　 　　11：00　　
　２ 　　 4：00　　　　 14：00　　　　　1:00　　　 1:00 　　　　 9:00
　３　　23：00　　　　　15：00 　　　　 7:00 　　 6:00 　 　 　 15:00

　さっそくありがとうございます。
　・勤務時間は8時間
　・休憩時間は1時間
　・深夜は22～5時（ただ残業としてではなく、勤務時間の中で深夜に該当する時間内に、
　　　　　　　　　　何時間勤務したのかを表記したいので、残業ではない場合がほとんどです）

すみません、私の質問の際に、多々説明不足点が多く申し訳ありますん。
運送業なので出勤時間もまちまちです・・・

尚勤務時間も長かったりするので、深夜時間を計算する際に22：00～29：00表記は難しいかと思われます

恐れ入りますが、ご存知の方宜しくお願いいたします