- ベストアンサー
Excel2003 深夜残業時間の計算
- Excel2003で深夜残業時間を計算する数式がうまく作成できないと困っています。
- 平日の早朝残業、基本時間、通常残業、深夜残業の時間帯ごとに残業時間を算出する必要があります。
- 現在使用している数式では特定の条件で深夜残業が算出されない場合があり、どのような数式を使用すればよいかわかりません。
- maikimaiki
- お礼率70% (31/44)
- その他MS Office製品
- 回答数7
- ありがとう数5
- みんなの回答 （7）
- 専門家の回答
質問者が選んだベストアンサー
その他の回答 (6)
- MarcoRossiItaly
- ベストアンサー率40% (454/1128)
＞0：00～5：00の間に出勤した場合が、0になってしまいました。 それは質問文にない時間パターンですので、当然、正しく計算しません。質問者さんの責任の範疇ですので、ご自分で必要なセルを補ってください。 重複する時間帯の長さ、例えば 5:00?8:30 という時間帯と 6:00?23:00 という時間帯が重複している範囲は 6:00?8:30 ですが、その長さ 2:30 を求める数式は、max(,min-max) ということになっています。回答者の責任ではありませんので、ご自分で応用してください。
お礼
回答を寄せてくださり、ありがとうございます。 質問文の中に考えられるパターンを提示せず申し訳ありませんでした。 自分でも色々勉強しないといけませんね。 どうもありがとうございました！
[No.2補足]へのコメント、 》 条件付けが全く出来ていないって事ですね 仰るとおりですね。 他に、例えば、08:25 に出社したら 08:30 までの５分間分の「早朝残業」を付けるのか否かという問題もあります。
お礼
アドバイスをくださったおかげで、こんがらがっていた頭を整理しなくては、と気付きました。 皆様にお付き合い頂き、感謝しています。 ありがとうございました！
- MarcoRossiItaly
- ベストアンサー率40% (454/1128)
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など） ですが数式のヒントを頂けましたので自分でも、もっと試してみます！ どうもありがとうございました！
- KURUMITO
- ベストアンサー率42% (1835/4283)
曜日などの表示は別として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時間となるのかは不明なのですが…。 ですが、面倒な式を考えて下さり感謝いたします！
当日の05:00より前に(例えば03:00)出社して、翌朝(例えば04:00)まで仕事をするなんてことは考えなくてよろしいのですか？ つまり、「出社は当日の05:00以降、かつ、退社は翌日の05:00より前」ということ？ 貴方の「説明がうまく出来ていないかもしれ」ないので、確認しています。
補足
早速ありがとうございます。 うまく説明できていなくてすみません。 確かに当日の05：00より前に出社して、翌朝4：00まで仕事をする、もあります・・・。 恥ずかしながら条件付けが全く出来ていないって事ですね・・・
- kuma310min
- ベストアンサー率40% (212/518)
考え方だけの説明になりますが、 ・作業用として、H列・I列を用意 ・退社が22：00以降ならば、その超過分を計算 → H列へ ・出社が5：00以前ならば、出社～5：00までを計算 → I列へ ↓ 上記2つを足す。（G列） ではダメなのでしょうか？ （どうしても作業用の列を使いたくない場合は、当然NGですけど）
お礼
作業列を使い算出する方法を教えてくださりありがとうございました！ こんがらがっていた頭を整理しなくては、と気付きました。 皆様にお付き合い頂き、感謝しています。 ありがとうございました！
補足
早速回答をありがとうございます。 作業列を使う方法ですね、確かにその方がきちんと整理されていいのかもしれません！ 退社と出社と分けて数式を作ってみたいと思います。 ありがとうございました！
お礼
皆様にお付き合い頂き、感謝しています。 本当にどうもありがとうございました！
補足
昨日は勤め先から帰宅してしまった為、確認が遅くなり申し訳ありません。 数式の修正まで寄せてくださりありがとうございます！ 早速、勤務時間を入力したところ、全ての時間で正しい深夜残業が算出出来ました！すごいですね・・・！ 自分では頭がこんがらがってしまい、お手上げだったので本当に助かりました。 ありがとうございました！