エクセルで深夜勤務明けが休日の場合の割増時間の計算についてです

2日をまたぐ深夜勤務明けの日が休日の場合、深夜0：00以降は休日割増賃金が発生する
のですが、エクセルでの時間計算方法はどのような式を設定すればいいでしょうか？
以下のように、出社と退社時間を入力してみています
（他にF列とG列では時間外と深夜割増の時間を計算させています）

勤怠管理できる表を作成しようとしていますが、知識不足で行き詰まってしまいました
教えて頂けたら大変助かります
どうぞ宜しくお願いします　


例)
　　　A　　 B　　　C　　　　　　D　　　　　E　　　　　F　　　　G　　　　H
1　 曜日　 休日　出社時間　　退社時間　　 休憩時間　　時間外　　深夜　　休日割増
2　　月　　　　　20：00　　30：00　　　1：00　　　1：00　　6：00　　0：00
3　　火　　　　　20：00　　31：00　　　1：00　　　2：00　　6：00　　　←ココです　　
4　　水 　 休日
5　　木　　　　　20：00　　30：00　　　1：00　　　1：00　　6：00　　3：00

質問者からの補足コメント

• 

  補足です
  
  深夜勤務で日をまたいだ2日目の仕事明けが休日だった場合に　0：00~23：59
  の間にかかって働いた部分の時間は、休日出勤の割増対象にあたるため、その時間を　
  B列の　”休日”　部分で判定して求められないものでしょうか
  
  説明不足かもしれませんが、宜しくお願いします

    補足日時：2017/02/07 19:07

No.2 ベストアンサー 回答者： yuji3690 回答日時： 2017/02/07 19:13

2日を跨ぐというのは、24時を超えるという意味ですか？

であるなら、退社時間が24時を超えていて、下の行に休日表示がある場合、退社時間-24時。それ以外は0。とすればいいと思います。

H3の例ですと、
=IF(D3>1,IF(B4="休日",D3-1,0),0)
となります。時間は1日を基準にして1時間は1/24と表されるので、24時=24/24=1です。
30時なら30/24です。

この回答へのお礼

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

まさに求めていた答えでした
私のつたない説明に答えていただき感謝です！！

お礼日時：2017/02/07 19:29

No.1 回答者： 銀鱗 回答日時： 2017/02/07 16:20

自分なら、勤務時間を細かく分ける。

でないと他で問題が起きた時に全体を把握できなくなる。

・通常勤務の出社時間からお昼の休憩時間まで。
・お昼休みが終わった時間から通常退勤時間まで。
・通常退勤時間から深夜残業開始時間まで。
・深夜残業開始時間から深夜残業終了時間まで。
・深夜残業終了時間から通常勤務の出社時間まで。
…のようにね。
本来はさらに
・深夜作業中の休憩時間を考慮したり、
・休日の場合の計算式を別にしたり、
・夜間勤務の場合の計算式を別にしたり、
・午後の休憩時間を反映させたり、
…してもっと細かく分ける。


・・・本題・・・
MAX関数とMIN関数を使うと良いでしょう。

　＝MAX("8:30",C2)
とすると、
　8：30より後に出社したらC2の時間が反映され、
　8：30より前に出社したら8：30が反映されます。

早出時間外は別に計算できます。
　＝"8:30"-MAX("7:00",MIN("8:30",C2))
　（（定時出社時間と出社時間を比較して小さいほうの時間）と深夜残業終了時間の7：00を比較して大きいほうの時間）を8：30から引くと早出時間外の時間になる。
　
　＝MIN("17:00",D2)
これで
　17：00より早く帰ったらD2の時間が反映され、
　17：00より遅く帰ったら17：00が反映されます。

時間外は別に計算できます。
　＝MIN("24:00",MAX("17:00",D2))-"17:00"
　（（退社時間と17：00を比較して大きいほうの時間）と24：00と比較して小さいほうの時間）から17：00を引くと残業時間になる。

こんなことを、チマチマやりましょう。
通常勤務の定時内ならお昼休みを挟んで午前と午後で別々に計算です。

※早出時間外の計算がちょっと特殊なだけです。
　時間外の計算方法でほとんどの時間を求めることができます。
　何から何を引くと求める時間になるのかを考慮し、MAX関数とMIN関数の使いどころを考えてください。

この回答へのお礼

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

時間帯を細かく分けるという考えはありませんでした
いただいたアイデアを生かせる様に考えてみます

お礼日時：2017/02/07 19:10