Excelで残業時間集計

質問

過去の質問も確認しましたが、分からなかったので質問させて下さい。
Excel（2000）で残業時間の集計をしたいのですが、うまく集計出来る関数が分かりませんでした。

【条件】
◆就業時間が9:00～17:30
◆休憩時間が12:00～13:00、20:30～21:30
◆就業時間より30分以上時間が経過している場合は残業時間に該当
◆22:00以降は深夜残業
◆土曜日は休憩時間を除く実労働時間が残業時間となる

【例１】
B2に平日の残業開始時間を入力し、C2に残業終了時間を入力すると、D2に残業時間、E2に深夜残業時間が表示される。
【例２】
B3に土曜日の残業開始時間を入力し、C3に残業終了時間を入力するとD3に残業時間、E3に深夜残業時間が表示される。

上記の例で関数を作るとすると、どのような関数が適当なのでしょうか？教えて下さい。宜しくお願い致します。

回答No.2

OKERAUMI

Ｎｏ１です。
すみません、土曜日の残業で、12：00以降の出社が認められる場合を考慮し忘れました。
残業時間の計算式について、
　-IF(NOT(A2="平日"),MIN(MAX(C2-$H$3,0),1/24),0),0)
の部分を
　-IF(NOT(A3="平日"),MIN(MAX(C3-$H$3,0),IF(B3<=$H$3,
　　1/24,MAX($H$3+1/24-B3,0))),0),0)
に修正します。
これでも20：30以降の出社が認められる場合は対応できませんが、ご了承下さい。

お礼

有難うございます。
確認させて頂きます。不明点がありましたら再度ご質問させて
下さい。取り急ぎ御礼まで。

補足

確認させて頂いたのですが、うまく出来ませんでした。
というのもNo1様のご回答に問題があるのではなく、
私の質問の仕方が間違えておりました。というのも、
出来れば私が作成した書式で関数をあてたいのです。
（先にお伝えしておけば良かったのです）
画像を添付致しますので再度教えて下さい。
お手数ですが、宜しくお願い致します。

回答No.1

OKERAUMI

色々とルールが発生しますが、こういうのは如何ですか？
なお私の例では、日曜、祝日は土曜日と同じ扱いになります。
【約束事】
　・平日の残業開始は「17：30」に固定
　・深夜 0：00は「24：00」と入力
　　※ 深夜 1：00は「25：00」
　・予め深夜開始、休憩開始（昼）、休憩開始（残）の時間のリストを作成する。
　　【例】　深夜残業開始　セルＨ２に「22：00」を入力
　　　　　　休憩開始（昼）　セルＨ３に「12：00」を入力
　　　　　　休憩開始（残）　セルＨ４に「20：30」を入力
残業時間　（セルＤ２に下式を入力）
　=IF(C2>=B2+0.5/24,MIN(C2-B2,$H$2-B2)-MIN(MAX(C2-$H$4,0),1/24)
　 -IF(NOT(A2="平日"),MIN(MAX(C2-$H$3,0),1/24),0),0)
深夜残業時間　（セルＥ２に下式を入力）
　=IF(C2>=$H$2,C2-$H$2,0)
　　※ 上記の式をコピーします。
　　※ 残業の式は長いので折り返しております。

お役に立てれば幸いです。

補足

eye6です。
画像を添付出来ませんでしたので、こちらの補足欄でご説明いたします。

A4に日付　B4に曜日、C4に残業開始時間、D4に残業終了時間、E4に残業時間計になります。R3に深夜時間、R4に休憩開始（昼）、R5に休憩開始（夜）です。ちなみに曜日は日、月、火・・・という表示の仕方です。
再度になりますが宜しくお願い致します。