エクセル勤務表を作成したい

質問

色々調べてみたのですが、力が足りずうまく行かないので教えてください。
(1)9：00～18：00が基本の労働時間です。
(2)18：00以降は残業
(3)昼休憩は12：00～13：00の一時間で、その分実働から引きたい
(4)12：00までの勤務は昼休憩を引きたくない
(5)13：00からの勤務は昼休憩を引きたくない
（12：00からの勤務は13：00からと同じになります）

今までは以下のような式を入れてました。
A列：日付（問題なし）
B列：曜日（問題なし）
C列：勤務開始時間（勤務する方が入力予定）
D列：勤務終了時間（勤務する方が入力予定）
E列：実働時間
=IF(C11="","",(D11-C11)-F11-G11)
F列：休憩時間（１時間）・・・・ここは無くてもいいです。
=IF(C11="","",IF(AND(D11<=TIME(12,0,0)),TIME(0,0,0),TIME(1,0,0)))
G列：残業時間（18：00以降）
=IF(D11<=TIME(18,0,0),"0:00",D11-TIME(18,0,0))

これが、いいレベルのものだとは思ってませんが
なんとか形になったのですが・・・

(6)9：00前に出勤も残業入れる

という条件を出されまして、困っています。
皆さんのお知恵を貸してください！

休憩時間は表示しなくても大丈夫です。
実働が、(4)、(5)の条件を満たしていて表示されることと
残業が9：00前までと18：00以降の合算で出ると良いのですが・・。

よろしくお願いします！

回答No.1

KURUMITO · ベストアンサー

時間帯を４つに分けて考えるのがよいでしょう。次の例を示しますので、参考にすればよいでしょう。
Ｂ1セルにはＡ時間、Ｃ1セルにはＢ時間、Ｄ1セルにはＣ時間、Ｅ1セルにはＤ時間と入力します。
Ａ2セルには開始時間、Ａ3セルには終了時間と入力します。
Ｂ2セルには例えば6:00、Ｂ3セルには9:00、Ｃ2セルには9:00、Ｃ3セルには12:00、Ｄ2セルには13:00、Ｄ3セルには18:00、Ｅ2セルには18:00、Ｅ3セルには例えば24:00とそれぞれ入力します。
Ａ5セルには出勤時間、B5セルには退勤時間、Ｃ5セルにはＡ時間、Ｄ5セルにはＢ時間、Ｅ5セルにはＣ時間、Ｆ5セルにはＤ時間、Ｇ5セルには実働時間と入力します。
Ａ6セル以降には出勤時間を、Ｂ6セル以降には退勤時間を入力します。
Ｃ6セルには次の式を入力してＦ6セルまでオートフィルドラッグしたのちに下方にもオートフィルドラッグします。
=IF($A6="","",IF((MIN($B6,B$3)-MAX($A6,B$2))<0,0,MIN($B6,B$3)-MAX($A6,B$2)))
Ｇ6セルには次の式を入力し、下方にオートフィルドラッグします。
=IF(A6="","",SUM(C6:F6))
一度ここに示した通りに試験をしてみてから実際に応用するとよいでしょう。

のようにすればよいでしょう。

お礼

ありがとうございます！いま試してみました。
その他の項目もあるので色々とアレンジしてみます！
大感謝です！

補足

改めまして、教えていただきありがとうございました！

9：00以前の出勤も残業に加える指示のほか、
深夜勤務の時間帯も入るようにせよ、と改めて指示がありましたが、
KURUMITOさんの方法なら、簡単に対応出来ました。

どうやら、かなり変則勤務の方々がいることが昨日判明しました。

当初、指示出しの方から頂いた表と指示は、どんどん変化するので
力不足で対応出来ませんでした。
色々勉強させて頂きました、本当に感謝です！

回答No.3

MackyNo1

以下のように分類するのが簡単なのではないでしょうか。

午前中の労働時間
=MIN("12:00"*1,D11)-MIN(C11,"12:00"*1)

午後の労働時間
=MAX("13:00"*1,D11)-MAX(C11,"13:00"*1)

18時以降の残業時間
=MAX(D11-"18:00",0)

9時前の残業時間
=MAX("9:00"-C11,0)

お礼

どうもありがとうございました！
9時前・午前・午後・18時以降と４列に区切って試してみました！

18時以降と9時前は、よい感じに時間トータルが出ました。

ただ、例えば朝6：00出勤したり、夜22：00まで残業したとすると
午前の列と午後の列の部分で
9時前からと18時以降の時間もカウントしてしまい、ダブってしまいます。

多分、ここから自分でアレンジしていけば良いのでしょうが、
時間がなく・・。
一番最初のお返事を頂いた方の方法で上手く行ったので
そちらで行きたいと思います！

MAXとMINの使い方が解っていなかったので、
今回大変勉強になり、感謝しています。
どうもありがとうございました！

回答No.2

okormazd

質問の範囲なら、
E列
=IF(OR(C2="",D2=""),"",IF(AND(C2>=TIME(9,0,0),D2>=TIME(18,0,0)),TIME(18,0,)-C2,IF(AND(C2>=TIME(9,0,0),D2<=TIME(18,0,0)),D2-C2,IF(AND(C2<=TIME(9,0,0),D2>=TIME(18,0,0)),TIME(18,0,0)-TIME(9,0,0),D2-C2)))-TIME(1,0,0))
G列
=IF(OR(C2="",D2=""),"",IF(AND(C2<TIME(9,0,0),D2>TIME(18,0,0)),TIME(9,0,0)-C2+D2-TIME(18,0,0),IF(AND(C2<TIME(9,0,0),D2<TIME(18,0,0)),TIME(9,0,0)-C2,IF(AND(C2>TIME(9,0,0),D2>TIME(18,0,0)),D2-TIME(18,0,0),""))))
でいいが、
添付の
7行以下のようなことはないのか。

お礼

回答、感謝いたします。
いま色々試行中です。また後でお返事しますね！

補足

改めまして、教えていただきましてありがとうございました！

・７行以降のことは、細かい時間は入れないようにしてもらいます。
・遅刻、早退、などありますし、
　残業に加えて深夜も入れるように再度指示がありました。
　どうやら、かなり変則に勤務する方もいるとのことでした。
　（後から指示が変わるのと、質問の時のフォーマットは指示出しを
　して下さった方の作表でした）

と、いうことで
最初の方の方法がベストなことが解り、作り変えました！

今回色々勉強させていただきました。
お時間を頂いたことに感謝いたします！
ありがとうございました！