Excel2003 深夜残業時間の計算

少し長い質問になりますが、よろしくお願いいたします。
出社・退社の時間から、早朝残業や夕方残業、深夜残業など、
残業時間を算出する数式を作成していますがどうにもうまく出来ません。

残業となる時間の切り分けは、平日は以下のようになっています。、
　早朝残業→5：00～8：30
　基本時間→8：30～17：00
　通常残業→17：00～22：00
　深夜残業→22：00～翌朝5：00

例えば平日、朝6：00出社→23：00退社であれば、
早朝残業が2.5、通常残業が5.0、深夜残業が1.0となります。

深夜残業を出す数式を、以下のように作成してみたのですが、うまく算出できません。
C列に出社、D列に退社、入力はコロンを挟んだ時間の形式で、表示形式は[h]:mmです。
A列に日付、B列に祝日のフラグ、数式はG列に入れています。

=IF(C10="",0,IF(OR(TEXT(A10,"aaa")="日",TEXT(A10,"aaa")="土",B10="祝"),0,IF(AND(D10>=VALUE("22:00"),C10<=VALUE("5:00")),ROUND((MIN("29:00",D10)-"22:00")*24,2),IF(AND(C10>=VALUE("0:00"),C10<VALUE("5:00")),ROUND((MIN("5:00",D10)-C10)*24,2),0))))

ですが、出社13:00→退社27:00のところで、深夜残業が0.0になってしまい、
数式の　IF(AND(D10>=VALUE("22:00"),C10<=VALUE("5:00"))をOR式に変更しました。

=IF(C10="",0,IF(OR(TEXT(A10,"aaa")="日",TEXT(A10,"aaa")="土",B10="祝"),0,IF(OR(D10>=VALUE("22:00"),C10<=VALUE("5:00")),ROUND((MIN("29:00",D10)-"22:00")*24,2),IF(AND(C10>=VALUE("0:00"),C10<VALUE("5:00")),ROUND((MIN("5:00",D10)-C10)*24,2),0))))

すると、出社0：00→退社18：30のところで、深夜残業が0.0になってしまいました。

両方をカバーできる数式はどのようになるのか、色々試しても出来ません。
お分かりになる方、いらっしゃいましたらお知恵を貸してください。
説明がうまく出来ていないかもしれません、分かりづらい文章で申し訳ありませんが
どうぞよろしくお願いいたします。

No.5 ベストアンサー 回答者： KURUMITO 回答日時： 2013/06/25 08:31

回答No３です。

ごめんなさい。一部を修正してください。
早朝残業のE２セルには次の式を入力して下方にドラッグコピーします。

=IF(C2="","",IF(AND(C2<D2,C2<"8:30"*1,D2>"5:00"*1),(MIN(D2,"8:30")-MAX(C2,"5:00"))*24,IF(AND(C2>D2,D2>"5:00"*1),(MIN(D2,"8:30")-"5:00")*24,"")))

通常残業のF２セルには前回同様に次の式を入力して下方にドラッグコピーします。

=IF(C2="","",IF(AND(C2<D2,D2>"17:00"*1),(MIN(D2,"22:00")-MAX(C2,"17:00"))*24,IF(AND(C2>D2,D2>"17:00"*1),(MIN(D2,"22:00")-"17:00")*24,"")))

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

=IF(C2="","",IF(AND(C2<D2,C2>="5:00"*1),(MAX(D2,"22:00")-MAX(C2,"22:00"))*24, IF(AND(C2<D2,C2>=0),(MIN(D2,"5:00")-C2+MAX(D2,"22:00")-MAX(C2,"22:00"))*24,IF(AND(C2>D2,D2<="22:00"*1),(MIN(D2,"5:00")+"24:00"-MAX(C2,"22:00"))*24,IF(AND(C2>D2,D2>"22:00"*1),(MIN(D2,"5:00")+MAX(D2,"22:00")-"22:00")*24,"")))))

この回答への補足

昨日は勤め先から帰宅してしまった為、確認が遅くなり申し訳ありません。

数式の修正まで寄せてくださりありがとうございます！
早速、勤務時間を入力したところ、全ての時間で正しい深夜残業が算出出来ました！すごいですね・・・！

自分では頭がこんがらがってしまい、お手上げだったので本当に助かりました。
ありがとうございました！

補足日時：2013/06/25 09:59

この回答へのお礼

皆様にお付き合い頂き、感謝しています。
本当にどうもありがとうございました！

お礼日時：2013/06/25 10:07

No.7 回答者： MarcoRossiItaly 回答日時： 2013/06/26 00:05

＞0：00～5：00の間に出勤した場合が、0になってしまいました。

それは質問文にない時間パターンですので、当然、正しく計算しません。質問者さんの責任の範疇ですので、ご自分で必要なセルを補ってください。

重複する時間帯の長さ、例えば 5:00?8:30 という時間帯と 6:00?23:00 という時間帯が重複している範囲は 6:00?8:30 ですが、その長さ 2:30 を求める数式は、max(,min-max) ということになっています。回答者の責任ではありませんので、ご自分で応用してください。

この回答へのお礼

回答を寄せてくださり、ありがとうございます。
質問文の中に考えられるパターンを提示せず申し訳ありませんでした。
自分でも色々勉強しないといけませんね。
どうもありがとうございました！

お礼日時：2013/06/26 13:18

No.6 回答者： mike_g 回答日時： 2013/06/25 09:49

[No.2補足]へのコメント、

》 条件付けが全く出来ていないって事ですね
仰るとおりですね。
他に、例えば、08:25 に出社したら 08:30 までの５分間分の「早朝残業」を付けるのか否かという問題もあります。

この回答へのお礼

アドバイスをくださったおかげで、こんがらがっていた頭を整理しなくては、と気付きました。
皆様にお付き合い頂き、感謝しています。
ありがとうございました！

お礼日時：2013/06/25 10:05

No.4 回答者： MarcoRossiItaly 回答日時： 2013/06/25 00:52

C4　6:00

D4　23:00
F2　5:00
F3　8:30
G2　=f3
G3　17:00
H3　22:00
I3　29:00
I4　=max(,min($D4,I$3)-max($C4,I$2))
J4　=sum(f4:i4)
K4　=d4-c4=j4

を、添付図では入力しています。数式を全部入力したら、時間が表示されているセル範囲を選択した状態で、右クリックなどから「セルの書式設定＞表示形式タブ＞ユーザー定義＞種類ボックス」に「[h]:mm」を入力。翌朝 1:00 を 25:00 などと表示します。

この回答への補足

昨日は勤め先から帰宅してしまった為、確認が遅くなり申し訳ありません。

回答を寄せて頂きありがとうございます！
画像まで付けてくださり、とてもわかりやすかったです。

ただ、サンプルの時間を入れてみましたら
0：00～5：00の間に出勤した場合が、0になってしまいました。
（2：15→15：15、0：00→10：30など）

ですが数式のヒントを頂けましたので自分でも、もっと試してみます！
どうもありがとうございました！

補足日時：2013/06/25 09:42

この回答へのお礼

皆様にお付き合い頂き、感謝しています。
ありがとうございました！

お礼日時：2013/06/25 10:05

No.3 回答者： KURUMITO 回答日時： 2013/06/24 17:07

曜日などの表示は別としてC２セルから下方に出社時間、D２セルから下方に退社時間が入力されているとしてE列に早朝残業を表示させるのでしたら次の式をE２セルに入力して下方にドラッグコピーします。

=IF(C2="","",IF(AND(C2<D2,C2<"8:30"*1),(MIN(D2,"8:30")-MAX(C2,"5:00"))*24,IF(AND(C2>D2,D2>"5:00"*1),(MIN(D2,"8:30")-"5:00")*24,"")))

F列に通常残業を表示させるのでしたら次の式をF２セルに入力して下方にドラッグコピーします。

=IF(C2="","",IF(AND(C2<D2,D2>"17:00"*1),(MIN(D2,"22:00")-MAX(C2,"17:00"))*24,IF(AND(C2>D2,D2>"17:00"*1),(MIN(D2,"22:00")-"17:00")*24,"")))

G列に深夜残業を表示させるのでしたらF２セルに次の式を入力して下方にドラッグコピーします。

=IF(C2="","",IF(AND(C2<D2,C2>="5:00"*1,D2>="22:00"*1),(MAX(D2,"22:00")-MAX(C2,"22:00"))*24,IF(AND(C2<D2,C2>=0,D2>="22:00"*1),(MIN(D2,"5:00")-C2+MAX(D2,"22:00")-MAX(C2,"22:00"))*24,IF(AND(C2<D2,C2>=0,D2<"22:00"*1),MIN(D2,"5:00")*24,IF(AND(C2>D2,D2<="22:00"*1),(MIN(D2,"5:00")+"24:00"-MAX(C2,"22:00"))*24,IF(AND(C2>D2,D2>"22:00"*1),(MIN(D2,"5:00")+MAX(D2,"22:00")-"22:00")*24,""))))))

なお、時間の入力については０：００から２３：５９までの入力とします。また２４時間以上の労働時間については考慮されていません。

この回答への補足

回答をありがとうございます！
深夜残業を出すにはこんなに長い条件が必要なのですね…勉強になります、ありがとうございます。

ただ、サンプルの時間を入れさせて頂きましたら、出社7:30→退社18:30でも深夜残業が5時間と出てしまいました。
回答して下さった式は私には難しく、中身を理解するには時間がかかってしまいそうなので、どこで5時間となるのかは不明なのですが…。

ですが、面倒な式を考えて下さり感謝いたします！

補足日時：2013/06/24 18:02

No.2 回答者： mike_g 回答日時： 2013/06/24 12:35

当日の05:00より前に(例えば03:00)出社して、翌朝(例えば04:00)まで仕事をするなんてことは考えなくてよろしいのですか？

つまり、「出社は当日の05:00以降、かつ、退社は翌日の05:00より前」ということ？

貴方の「説明がうまく出来ていないかもしれ」ないので、確認しています。

この回答への補足

早速ありがとうございます。
うまく説明できていなくてすみません。
確かに当日の05：00より前に出社して、翌朝4：00まで仕事をする、もあります・・・。
恥ずかしながら条件付けが全く出来ていないって事ですね・・・

補足日時：2013/06/24 13:08

No.1 回答者： kuma310min 回答日時： 2013/06/24 12:02

考え方だけの説明になりますが、

・作業用として、H列・I列を用意
・退社が22：00以降ならば、その超過分を計算　→　H列へ
・出社が5：00以前ならば、出社～5：00までを計算　→　I列へ
　↓
上記2つを足す。（G列）

ではダメなのでしょうか？

（どうしても作業用の列を使いたくない場合は、当然NGですけど）

この回答への補足

早速回答をありがとうございます。
作業列を使う方法ですね、確かにその方がきちんと整理されていいのかもしれません！
退社と出社と分けて数式を作ってみたいと思います。
ありがとうございました！

補足日時：2013/06/24 13:10

この回答へのお礼

作業列を使い算出する方法を教えてくださりありがとうございました！
こんがらがっていた頭を整理しなくては、と気付きました。
皆様にお付き合い頂き、感謝しています。
ありがとうございました！

お礼日時：2013/06/25 10:02