エクセルで勤怠時間(一部分の)計算方法

質問

エクセルで社員の勤怠時間の計算を行いたいのですが
始業から終業までの、全ての時間ではなく
始業から終業までの間で行っている、一部の時間（開始時間～終了時間）だけを取り出し計算し
さらにそれを、日中時間・残業時間・深夜残業時間に振り分けを行いたいです。
始業時間は、バラバラで午前の人もいれば午後の人もいます。
また、始業＝開始でない場合もあります。
色々と類似質問を参照し、式を入れているのですが振り分けがうまくいきません
以下に振り分けの際の条件と例を記載します。

C1：始業時間（手入力）
D1：終業時間（C1+TIME(9,0,0)）
E1：開始時間（手入力）
F1：終了時間（手入力）
G１,H１,I１：各時間帯から控除する休憩時間（手入力）
J１：日中時間（始業時間～終業時間内の、8時間までの開始時間～終了時間）　※ここの式
K1：残業時間（22時までの終業時間を超過した、開始時間～終了時間）　※ここの式
L１：深夜残業時間（22時以降の終業時間を超過した、開始時間～終了時間）　※ここの式
例）
(1)始業/9：00　終業/18：00　開始/13：00　終了/23：00　休憩１/0：30　休憩2/0：15　休憩3/0：00
結果→日中/4：30　残業/3：45　深夜残業/1：00
(2)始業/13：00　終業/22：00　開始/13：00　終了/24：00　休憩１/1：00　休憩2/0：00　休憩3/0：15
結果→日中/8：00　残業/0：00　深夜残業/1：45

お知恵をお貸し下さい

回答No.3

kagakusuki

ANo.2です。

>こちらを以下の様に変更したいのですが、どのようにすればよろしいでしょうか？
>例）「6：00→6.00」　「6：15→6.25」　「6：30→6.50」
>　　「6：45→6.75」「6：10→6.17」「6：25→6.42」

　それでしたら、各セルに入力する関数を以下の様に修正して下さい。

　J2セルに入力する関数

=IF(COUNT($C2,$D2,$E2,$F2,$G2)=5,(IF(MAX(MIN($D2,$F2,"22:00"+0),"5:00"+0)-MIN(MAX($C2,$E2,"5:00"+0),"22:00"+0)-$G2>0,IF(MAX(MIN($D2,$F2,"22:00"+0),"5:00"+0)-MIN(MAX($C2,$E2,"5:00"+0),"22:00"+0)-$G2>"8:00"+0,"8:00"+0,MAX(MIN($D2,$F2,"22:00"+0),"5:00"+0)-MIN(MAX($C2,$E2,"5:00"+0),"22:00"+0)-$G2),0))*24,"")

　K2セルに入力する関数

=IF(COUNT($J2,$H2)=2,(IF(AND($F2>$D2,$D2<"22:00"+0),MIN($F2,"22:00"+0)-MAX($D2,$E2),0)+IF(AND($E2<$C2,$C2>"5:00"+0),MIN($F2,$C2)-MAX($E2,"5:00"+0),0)+IF($J2<8,0,MAX(MIN($D2,$F2,"22:00"+0),"5:00"+0)-MIN(MAX($C2,$E2,"5:00"+0),"22:00"+0)-$G2-"8:00")-IF(OR(AND($F2>$D2,$D2<"22:00"+0),AND($E2<$C2,$C2>"5:00"+0),$J2>=8),$H2,0))*24,"")

　L2セルに入力する関数

=IF(COUNT($C2,$D2,$E2,$F2,$I2)=5,(IF($F2>"22:00"+0,MIN($F2,"29:00"+0)-MAX($E2,"22:00"+0),0)+IF($E2<"5:00"+0,MIN($F2,"5:00"+0)-$E2,0)-IF(OR($F2>"22:00"+0,$E2<"5:00"+0),$I2,0))*24,"")

回答No.2

kagakusuki

確認したいのですが、以下の様な条件と考えれば宜しいのでしょうか？

●C列に始業時間、E列に開始時間、F列に終了時間、G列に休憩時間1(日中時間時間から控除する休憩時間)、H列に休憩時間2(残業時間から控除する休憩時間)、I列に休憩時間3(深夜残業時間から控除する休憩時間)を手入力すると、
D列に終業時間、J列に日中時間、K列に残業時間、L列に深夜残業時間が自動的に表示される様にする。

●各列の1行目は、その列の項目名が入力されていて、実際のデータは2行目以下に入力されたり、自動表示される。

●終業時間は必ず始業時間の９時間後

●日中時間は、始業時間～終業時間の時間帯と、開始時間～終了時間の時間帯が重なっている時間から、休憩時間1を差し引いた値。
　但し、その値が8時間を超える場合には、日中時間は一律で8時間とする。

●日中時間で８時間を超えている分に関しては、残業時間扱いとなる。

●２2時～24時の時間帯、及び0時～5時（24時～29時を含む）に行われた労働の時間は、始業時間～終業時間の時間帯と重なっているか否かには関係なく、一律で深夜残業時間となる。

●終業時間以降で、22時以前に行われた労働の時間は残業時間となる。

●早出で始業時間以前に行われた労働の時間の内、5時以降に行われた労働の時間もまた残業時間としてカウントする。

●始業時間が5時よりも前の時刻となったり、終業時間が22時よりも後の時刻となる場合もあり得る。

　もし、上記の条件で構わないのでしたら、以下の様にされると宜しいかと思います。

　まず、J2セルに次の関数を入力して下さい。

=IF(COUNT($C2,$D2,$E2,$F2,$G2)=5,IF(MAX(MIN($D2,$F2,"22:00"+0),"5:00"+0)-MIN(MAX($C2,$E2,"5:00"+0),"22:00"+0)-$G2>0,IF(MAX(MIN($D2,$F2,"22:00"+0),"5:00"+0)-MIN(MAX($C2,$E2,"5:00"+0),"22:00"+0)-$G2>"8:00"+0,"8:00"+0,MAX(MIN($D2,$F2,"22:00"+0),"5:00"+0)-MIN(MAX($C2,$E2,"5:00"+0),"22:00"+0)-$G2),0),"")

　次に、K2セルに次の関数を入力して下さい。

=IF(COUNT($J2,$H2)=2,IF(AND($F2>$D2,$D2<"22:00"+0),MIN($F2,"22:00"+0)-MAX($D2,$E2),0)+IF(AND($E2<$C2,$C2>"5:00"+0),MIN($F2,$C2)-MAX($E2,"5:00"+0),0)+IF($J2<"8:00"+0,0,MAX(MIN($D2,$F2,"22:00"+0),"5:00"+0)-MIN(MAX($C2,$E2,"5:00"+0),"22:00"+0)-$G2-"8:00")-IF(OR(AND($F2>$D2,$D2<"22:00"+0),AND($E2<$C2,$C2>"5:00"+0),$J2>="8:00"+0),$H2,0),"")

　次に、L2セルに次の関数を入力して下さい。

=IF(COUNT($C2,$D2,$E2,$F2,$I2)=5,IF($F2>"22:00"+0,MIN($F2,"29:00"+0)-MAX($E2,"22:00"+0),0)+IF($E2<"5:00"+0,MIN($F2,"5:00"+0)-$E2,0)-IF(OR($F2>"22:00"+0,$E2<"5:00"+0),$I2,0),"")

　次に、J2～L2の範囲をコピーして、同じ列の3行目以下に貼り付けて下さい。

　以上です。

お礼

kagakusukiさん
ご回答ありがとうございます、急な出張に出ており返答返せず申し訳有りません。

記入頂きました上記9個の条件で大丈夫と思われます。
お教え頂きました式を導入し、データの入力を順次行っており
まだ計算結果違いなどは出てきておりませんが
もうひとつ以下を追加としてお教えいただければ幸いです。

現状お教え頂いた式では、日中・残業・深夜の結果が「XX:XX」の結果で出力されております。
こちらを以下の様に変更したいのですが、どのようにすればよろしいでしょうか？
例）「6：00→6.00」　「6：15→6.25」　「6：30→6.50」
　　「6：45→6.75」「6：10→6.17」「6：25→6.42」

お手数をおかけしますがよろしくお願いします。

回答No.1

KURUMITO

C1セルからL1セルまでにはお示しの各項目名が有るとして、各データが2行目から下方にあるとしたらJ2セルには次の式を入力して下方にドラッグコピーします。

=IF(COUNT(C2:F2)<>4,"",MIN(D2,F2)-E2-G2)

K2セルには次の式を入力して下方にドラッグコピーします。

=IF(COUNT(C2:F2)<>4,"",MIN(F2,"22:00")-D2-H2)

L2セルには次の式を入力して下方にドラッグコピーします。

=IF(COUNT(C2:F2)<>4,"",MAX(F2,"22:00")-"22:00"-I2)