残業時間の計算

エクセルは使い慣れていない素人です。

エクセルの表計算下記のように日付を超えた１つの作業に対しての総残業時間集計をしたいのですがうまく出来ません。
ご存知の方がいらっしゃれば教えてください。

通常勤務時間帯は8:00～17:00（昼休憩１時間）通常勤務時間８時間です。
複数の人間が一つの作業を行う場合として計算をしたいのです。
手入力で打ち込む情報は開始日時と終了日時のみです。

開始日時　2012/07/23　 8：00
終了日時　2012/07/25　21：00

通常勤務総時間　２４時間
総残業時間　　　　３４時間

上記のように、通常勤務時間内なのか残業時間帯なのかを判別して日付を超えても集計され
その作業にかかった時間の通常勤務総時間と総残業時間が自動的に計算されるようにするにはどのような計算式になるのでしょうか？
よろしくお願いいたします。

No.3 回答者： don9don9 回答日時： 2012/07/23 18:11

作業列を沢山使う、非常に面倒な方法になりますがご容赦ください。

開始日時がA1セルとします。
終了日時がB1セルとします。
それと質問文には明確な記述がありませんでしたが、休憩は12:00～13:00とします。

考え方として、まず開始日時～終了日時の期間を、丸一日（0:00～24:00）が作業時間で埋まる日とそうでない日に分けます。
丸一日が作業時間で埋まる日は、通常勤務8h、休憩1h、残業時間15hで固定。
そうでない日というのは、開始日当日と終了日当日の最大2日になるわけですが、開始日は開始時刻～24:00、終了日は0:00～終了時刻を作業時間とみなします。
そしてこの作業時間が0:00～8:00、8:00～17:00、12:00～13:00、17:00～24:00の各時間帯とどれだけ重なっているかをそれぞれ計算します。

C1セルの式　→　開始日時～開始日の24:00までの時間数
=DATE(YEAR(A1),MONTH(A1),DAY(A1)+1)-A1
D1セルの式　→　終了日の0:00～終了時刻までの時間数
=B1-DATE(YEAR(B1),MONTH(B1),DAY(B1))
E1セルの式　→　初日と最終日を除いた日数（丸一日が作業時間で埋まる日の数）
=(B1-A1)-(C1+D1)
F1セルの式　→　開始日時より、時刻のみを抜き出す
=(HOUR(A1)&":"&MINUTE(A1))*1
G1セルの式　→　終了日時より、時刻のみを抜き出す
=(HOUR(B1)&":"&MINUTE(B1))*1
H1セルの式　→　初日の作業時間のうち0:00～8:00と重なる時間数
="8:00"-MIN("8:00",F1)
I1セルの式　→　初日の作業時間のうち8:00～17:00と重なる時間数
="17:00"-MAX("8:00",MIN("17:00",F1))
J1セルの式　→　初日の作業時間のうち12:00～13:00と重なる時間数
="13:00"-MAX("12:00",MIN("13:00",F1))
K1セルの式　→　初日の作業時間のうち17:00～24:00と重なる時間数
="24:00"-MAX("17:00",F1)
L1セルの式　→　最終日の作業時間のうち0:00～8:00と重なる時間数
=MIN("8:00",G1)
M1セルの式　→　最終日の作業時間のうち8:00～17:00と重なる時間数
=MIN("17:00",MAX("8:00",G1))-"8:00"
N1セルの式　→　最終日の作業時間のうち12:00～13:00と重なる時間数
=MIN("13:00",MAX("12:00",G1))-"12:00"
O1セルの式　→　最終日の作業時間のうち17:00～24:00と重なる時間数
=MIN("24:00",MAX("17:00",G1))-"17:00"

以上が作業列です。

通常勤務総時間の式は
=IF("8:00"*E1+I1-J1+M1-N1<0,"0:00"*1,"8:00"*E1+I1-J1+M1-N1)
総残業時間の式は
=IF("15:00"*E1+H1+K1+L1+O1<0,"0:00"*1,"15:00"*E1+H1+K1+L1+O1)
となります。

作業時間が日をまたがない場合、作業列の値がおかしくなる可能性はありますが、総時間数の式で帳尻が合うはずですので、そこは無視してください。

No.2 回答者： siremono2496 回答日時： 2012/07/23 18:08

> 通常勤務時間、残業時間は、毎日変わらず、いかなる時も絶対に変化する事はありません。

なるほど、そういうことでしたか。でしたら、8:00～21:00の勤務時間を日数でかければできますね。ものすごく平易なやり方を回答しておきます。

セルA1（表示形式：日付）
2012/07/23

セルA2（表示形式：日付）
2012/07/25

セルA3（表示形式：標準）
=A2-A1+1

セルB1（表示形式：時刻）※始業時刻
8:00

セルB2（表示形式：時刻）※終業時刻
21:00

セルC1（表示形式：時刻）※休憩時間
1:00

セルC2（表示形式：時刻）※一日の通常勤務時間
8:00

セルB4（表示形式：時刻）※総通常勤務時間
=C2*A3

セルB5（表示形式：時刻）※総稼働時間
=(B2-B1-B3)*A3

ところで総稼働時間は34時間じゃなくて36時間になると思いますが、何かまだ条件がありますか？

No.1 回答者： siremono2496 回答日時： 2012/07/23 15:05

それだけの情報では、残業時間は求められないのでは？毎日、始業と終業時間を入れないとダメでしょう。

初日の始業と最終日の終業時間だけでは、初日の終業時間と二日目の始業終業時間、三日目の始業時間が解りません。よって勤務時間そのものが求められません。その三日間が全て同じ始業・終業時間であれば、初日だけ計算して3倍すれば出来上がりですが、日々違った時間に終業だったらそうはいきません。

自動計算するにはあまりにも条件が不明確なので、それをもっとはっきりさせてください。

この回答への補足

私の説明が不足いたようですみません

通常勤務時間8:00～17:00（昼休憩１時間）８時間
通常勤務時間、残業時間は、毎日変わらず、いかなる時も絶対に変化する事はありません。

開始日時　2012/07/23　 8：00
終了日時　2012/07/25　21：00
上記入力の答えを手計算で計算した結果が、
通常勤務総時間　２４時間
総残業時間　　　３４時間
となり、同じ結果となるように表計算を行いたいのですがよろしくお願いします。

補足日時：2012/07/23 16:48