エクセル２０１０ 深夜残業時間の計算

出社・退社の時間から、残業、深夜残業など、
残業時間を算出する数式を作成していますがどうにもうまく出来ません。

残業となる時間の切り分けは、平日は以下のようになっています。、
　基本時間→8：30～17：15
　残業→5：00～8：30 と17：15～22：00
　深夜残業→22：00～翌朝5：00
　休憩時間→0:45(C1) 基本勤務時間→8:00(B1)

例えば平日、朝6：00出社→23：00退社であれば、
早朝残業が2:30、通常残業が4:45、計7:15です。深夜残業が1:00となります。

F欄に時間外、　G欄に深夜残業としています。
現状は時間外を出すのに　　=SUM(E5-D5)-$B$1-$C$1-G5　と単純にしてます。(G5は手入力)
これを自動で入力できたらと考えています。
5:00前に出勤することはほとんどありませんが、22:00を超えることはあります。
ただし、24:00を超えると翌日計算になります。

過去欄を参考にしようにも理解できなく困っています。
http://oshiete.goo.ne.jp/qa/8147465.html
よろしくお願いいたします。

No.3 ベストアンサー 回答者： MackyNo1 回答日時： 2013/12/22 10:52

深夜部分の残業時間を計算するなら、以下のような数式で早朝(5：00以前）部分と夜間（22：00以降）部分の深夜残業を足し算するのが簡単です。

（退出時間が翌日になる場合は25：00のような形式で入力した場合）。

=5/24-MIN(D5,5/24)+MAX(E5,22/24)-22/24

退出時間を「25：00」ではなく通常の「1：00」のように入力する場合は、以下の式で計算することができます。

=5/24-MIN(D5,5/24)+MAX((E5<D5)+E5,22/24)-22/24

ところで、深夜部分がメインの勤務がある場合、休憩時間などは考慮しなくてもよいのでしょうか？

この回答への補足

昼休憩のみで後は考慮しません。
ありがとうございます。

補足日時：2013/12/22 17:09

この回答へのお礼

回答ありがとうございます。
1,2の方のも良かったのですが、MackyNo1さんのものが、iPadやiphoneでエラー無しで見えましたのでベストアンサーに選ばせていただきます。
御陰様で助かりました。

お礼日時：2013/12/22 17:21

No.5 回答者： MackyNo1 回答日時： 2013/12/22 17:23

>昼休憩のみで後は考慮しません。

このケースだと、午前中のみ、または午後のみの勤務の時は（出社時間が1時以降または退社時間が12時以前の場合）、休憩時間は不要になるのではないでしょうか？（単純に休憩時間を引き算する数式だと勤務時間の計算に誤差が出るのでは？）

No.4 回答者： lv4u 回答日時： 2013/12/22 15:47

私も、以前にエクセルで入力された作業時間を計算する仕事をしました。

それまで総務の方が行っていたのと同様な計算方法をする必要があったため、エクセルに式を入れるだけではうまくいかず、ＶＢＡのプログラムを作成し、ボタンを押すとプログラムが呼び出されて計算するようにしました。

休憩時間とか、代休とかいろいろと考慮する必要があるので、それらにすべて対応するには、やはりプログラムを記述する必要があると思います。

No.2 回答者： bunjii 回答日時： 2013/12/21 22:32

>私の式(=SUM(E5-D5)-$B$1-$C$1-G5)から見れば、１だと思ったんですが、違うのですね。

その式のSUM関数は無意味で、単純にE5-E4と同等です。
尚、総勤務時間から規定勤務時間と休憩を差し引いただけなら深夜勤務が時間外の内数ですが、G5（深夜）を差し引いていますので２項の考え方になります。

>深夜残業の式をお願いいたします。
早出="8:30"-MAX("5:00",出勤時刻)　から類推できませんか？
深夜に掛かる早出="5:00"-MIN("5:00",出勤時刻)
残業=MIN("22:00",退出時刻)-"17:15"　から類推すれば
深夜に掛かる残業=MAX("22:00",退出時刻)-"22:00")　となります。

MAX関数は引数（括弧内の値）のうち1番大きい値を返します。
MIN関数は引数の内1番小さい値を返します。
MAX関数、MIN関数の代わりにIF関数を使う方法もありますがIF関数より分かり易いように思います。

この回答へのお礼

何回も回答ありがとうございます。
bunijiさんの回答も良かったのですが、他者様のものがipad等でスムーズに見えました。
でも、御陰様でいい勉強になりました。

お礼日時：2013/12/22 17:25

No.1 回答者： bunjii 回答日時： 2013/12/21 16:38

>残業→5：00～8：30 と17：15～22：00

>深夜残業→22：00～翌朝5：00
時間外（早出・残業）と深夜勤務の計算で考え方が2通りあります。

１．深夜勤務を時間外の内数で考える計算方法
２．通常の時間外と深夜勤務となる時間外を区別して計算する方法

あなたの質問は２の方法と解釈します。

>過去欄を参考にしようにも理解できなく困っています。
時間外は早出と残業に分けて計算します。

早出="8:30"-MAX("5:00",出勤時刻)
MAX関数で5:00前に出社したときは5:00～8:30（3時間30分）までが早出時間数となり、5:00以降に出社したときは出社時刻～8:30までの時間数となります。

残業=MIN("22:00",退出時刻)-"17:15"
MIN関数で22:00以降の退出のとき17:15～22:00（4時間45分）までが残業時間数となり、22:00までに退出したときは17:15～退出時刻までの時間数となります。

深夜勤務も時間外と同様に計算すれば目的に合うはずです。
他人が作成した計算式を理解もせずに利用すると思わぬ落とし穴に落ちますので注意してください。

この回答への補足

お返事ありがとうございます。

１．深夜勤務を時間外の内数で考える計算方法
２．通常の時間外と深夜勤務となる時間外を区別して計算する方法
私の式(=SUM(E5-D5)-$B$1-$C$1-G5)から見れば、１だと思ったんですが、違うのですね。

人様(高度な)の式を理解できる知識がありません。
回答いただいた理屈は分かるのですが、「早出="8:30"-MAX("5:00",出勤時刻)」「残業=MIN("22:00",退出時刻)-"17:15"」このような式がたてられないのです。
できましたら、深夜残業の式をお願いいたします。

補足日時：2013/12/21 20:13