エクセルを使った就業時間の計算について

質問

エクセルを使った就業時間の計算で質問です。
始業時間と終業時間の差し引きで拘束時間を計算して、
そこから休息時間を差し引き実働時間を計算する所までは
出来たのですが、実働時間から残業と深夜と深夜残業を自動で求めることが出来ません。

割り振りの条件は・・・
[深夜]当日0時から当日5時迄
[残業]当日5時から当日22時の間で、当日の実働が8時間を越えた部分。
[深夜残業]当日22時から29時の間で、当日の実働が8時間を越えた部分。

上記の条件で、自動計算をする為の式を教えて頂けませんでしょうか？
※画像の様な形で自動計算できれば理想です。

よろしくお願いします。

回答No.7

KURUMITO · ベストアンサー

回答No6です。
深夜時間は次の式になりますね。

=IF(COUNT(D5:F5)<>2,"",IF(D5<"5:00"*1,MIN(F5,"5:00")-MAX(D5,"0:00"),0))

他の時間についての式は前に示した通りです。

お礼

回答ありがとうございます。

完璧でした！ありがとうございました！

回答No.6

KURUMITO

最初のご質問では4時に始業で25時で終わって深夜時間は1時間となっていましたね。0時から5時までが深夜ならば2時間が正しいのではないでしょうか？　私は1時間の答えとなるように始業の時間を考慮せずに最初の式を立てました。補足では２時の始業にしたら正解は3時間ですとなっていました。4時間が正解ではないのですか？

補足

回答ありがとうございます。

＞0時から5時までが深夜ならば2時間が正しいのではないでしょうか？

最初の質問では、4時始業で25時終業です。振り分けの条件で記載しておりますが、
１．当日の0時から5時が深夜です。
２．当日の５時から22時までの間で、当日の実働が8時間（9時間の拘束）を越えた部分が残業です。
３．当日の22時から29時までの間で、当日の実働が8時間（9時間の拘束）を超えた部分が深夜残業です。

ですので、
4時始業の場合は、5時までの１時間が深夜です。
5時以降22時迄で、始業から9時間の拘束時間を越えた部分。8時間が残業です。
そして、そこから25時迄の3時間が深夜残業です。

ですので、始業が2時の場合は、5時迄の3時間が深夜となります。
深夜残業は、当時の残業が深夜に掛かる場合が条件ですので、深夜とは違う意味になります。

ややこしいのですが、お力頂けたら助かります。
よろしくお願い致します。

回答No.5

KURUMITO

回答No4です。深夜時間について補足の中で次のような文章がありますね。
始業を2:00に変えてみたのですが、深夜が1：00と表示されました。
3：00と計算されると正しいのですが・・・。
2時から25時までの勤務で0時からの深夜時間は1時間と計算する式を使っていました。そうではなく答えは3時間ですとのことですが始業開始が深夜に始まるときの時間を考慮しておりませんでした。その時間を考慮するとなれば次のような式になります。

=IF(COUNT(D5:F5)<>2,"",MIN(F5,"29:00")-MAX(D5,"24:00")+MIN(F5,"5:00")-MAX(D5,"0:00"))

回答No.4

KURUMITO

H5セルには次の式を入力して下方にオートフィルドラッグします。

=IF(COUNT(D5:F5)<>2,"",F5-D5)

J5セルには次の式を入力して下方にオートフィルドラッグします。

=IF(COUNT(D5:F5)<>2,"",H5-"1:00")

L5セルには次の式を入力して下方にオートフィルドラッグします。

=IF(COUNT(D5:F5)<>2,"",IF(MIN(F5,"22:00")-MAX(D5,"5:00")-"1:00">"8:00"*1,MIN(F5,"22:00")-MAX(D5,"5:00")-"1:00"-"8:00",""))

N5セルには次の式を入力して下方にオートフィルドラッグします。

=IF(COUNT(D5:F5)<>2,"",MIN(F5,"29:00")-MAX(D5,"24:00"))

P5セルには次の式を入力して下方にオートフィルドラッグします。

=IF(COUNT(D5:F5)<>2,"",IF(J5>"8:00"*1,MIN(F5,"29:00")-MAX(D5,"22:00"),0))

いずれのセルも表示形式で時刻にします。

補足

回答ありがとうございます！

早速試したみたのですが、始業時間と終業時間を変えたところ、深夜の計算が正しく計算されなく
なってしましました。

始業を2:00に変えてみたのですが、深夜が1：00と表示されました。
3：00と計算されると正しいのですが・・・。

もし、お分かりでしたら教えて頂けますでしょうか？

よろしくお願いします。

回答No.3

nattocurry

言い忘れましたが、計算するに当たって、「実働が8時間を超えた」を「拘束が9時間を超えた」に置き換えています。

回答No.2

nattocurry

L5に
=MAX(0,MIN(VALUE("22:00"),F5)-MAX(VALUE("5:00"),D5)-VALUE("9:00"))
N5に
=MAX(0,VALUE("5:00")-D5)
P5に
=MAX(0,MIN(VALUE("29:00"),F5)-MAX(VALUE("22:00"),D5+VALUE("9:00")))

でどうでしょう？

補足

回答ありがとうございます。

他のセルでも時間を変えて試してみました。キチンと計算されて出てきました！
助かりました。

それと、合わせてお聞きしたいのですが、始業と終業を入力しない場合でも
残業8:00 深夜5:00　深夜残業7:00　が表示されてしまいます。
時間を未入力の場合は、残業等の時間が表示されないようには出来るのでしょうか？

ご存知でしたら、教えて下さい。
宜しくお願いします。

回答No.1

nattocurry

添付画像の、残業8時間、深夜1時間、深夜残業3時間、というのは、どのように計算したのでしょうか？

補足

表示されている時間は手入力しただけです。計算はしていません。