EXCELで休憩を加味した深夜労働時間を計算する

深夜労働時間の算出 Excel関数
深夜労働時間の算出

深夜労働時間の計算に関する問い合わせ

勤務表(深夜)

勤務表(深夜)

上図の様なEXCELの勤務データから1日毎の深夜残業時間を計算したいのですが、どのような数式を設定すれば、深夜残業時間が計算できるかイメージが全く思い浮かばないんです。どうすれば計算できるか、その方法を教えてもらえませんか?

そうだね関数ひとつで簡単に計算できるものではないね。

それより、深夜残業時間を計算するって言ってるけど、深夜残業時間の定義は理解できてるの?

そもそも、深夜残業って表現がおかしいとおもうのだけど。

深夜残業時間の定義ですか?

22:00から5:00の間に労働した時間です。

この時間帯に労働した場合は通常時間帯の賃金に対して25%割増しなくてはなりませんので、その時間帯に労働した時間を計算したいのです。

深夜労働時間の定義は理解していたんだね。でも、夜勤の場合などその時間帯が所定労働時間帯の勤務となることもあるから、深夜残業時間というのはおかしいよね。正しくは深夜労働時間じゃないかな。

そうなんですけど、深夜残業時間と呼んでいる人が多いので...

それよりもどうやれば深夜残業時間...じゃなくて、深夜労働時間が計算できるのか教えて下さい。

上図の2024年12月30日(11行目)の深夜労働時間を計算する数式は次のようになるよ。

=”5:00″-MIN(“5:00”,C11)+MIN(“29:00”,H11)-MIN(MAX(“22:00”,C11),H11)

計算式の内容はいまいち理解できないですけど、深夜労働時間が算出できました

深夜労働時間帯に休憩した時間を加味して深夜労働時間を計算する

ありがとうございました。2024年12月30日(11行目)は休憩時間がないので、教えてもらった数式で計算できましたが、2024年12月31日(12行目)は休憩時間があるので、これを加味した深夜労働時間を計算するのはどうすればいいのですか?

簡単なことじゃない。深夜労働時間帯にどれだけの時間休憩をとったか計算して、その時間を深夜労働時間から引いたらいいだけでしょ。

すみません、頭固いですね。深夜労働時間帯に休憩した時間を計算するには、先ほど、教えてもらった数式の勤務開始時間(C列)の部分を休憩開始時間(D列)に変更し、勤務終了時間(H列)を休憩終了時間(G列)に変更すればいいのですよね。

=”5:00″-MIN(“5:00”,D12)+MIN(“29:00”,G12)-MIN(MAX(“22:00”,D12),G12)

そして、深夜労働時間からこの深夜帯休憩時間を引けば、実際の深夜労働時間が計算できますね。=”5:00″-MIN(“5:00”,C12)+MIN(“29:00”,H12)-MIN(MAX(“22:00”,C12),H12)-“5:00”-MIN(“5:00”,D12)+MIN(“29:00”,G12)-MIN(MAX(“22:00”,D12),G12)

正解! やればできるじゃない。

深夜労働時間計算式の解説

深夜労働時間の算出はMIN関数とMAX関数を使用します。その解説を以下に記載します。

深夜労働対象時間を算出する

早出における深夜労働時間を算出する

“5:00”-MIN(“5:00”,C12)

勤務開始時刻が深夜労働時間帯最終時刻よりも早い場合はその勤務開始時刻が戻り値となり、遅い場合は深夜労働時間帯最終時刻である5:00が戻り値となります。その戻り値を深夜労働時間帯最終時刻から引いた時間が早出における深夜労働時間として算出します。

深夜労働が終了した時刻を判断する

MIN(“29:00,H12)
MIN関数の戻り値は勤務終了時刻(H12)が深夜労働時間帯終了時刻の29:00(翌朝5:00)よりも大きければ、29:00(翌朝5:00)となり、小さければ勤務終了時刻となります。

深夜労働を開始した時刻を判断する

MIN(MAX(“22:00”,C12),H12)

まず、MAX関数の戻り値ですが、勤務開始時刻(C12)が深夜労働時間帯開始時刻の22:00よりも小さければ、22:00となり、大きければ勤務開始時刻となります。次にMIN関数の戻り値ですが、MAX関数の戻り値である深夜時間帯勤務開始時刻が勤務終了時刻よりも小さければ勤務終了時刻が深夜勤務開始時刻となり、大きければMAX関数の戻り値が深夜勤務開始時刻となります。

深夜労働時間を算出する

上で求めた深夜労働時間帯終了時刻MIN(“29:00”,H12)から深夜勤務開始時刻MIN(MAX(“22:00”,C12),H12)を引いた値が深夜労働時間となります。

深夜労働対象時間帯の休憩時間を算出する

深夜労働対象時間帯の休憩終了時刻を算出する

MIN(“29:00,G12)
MIN関数の戻り値は休憩終了時刻(G12)が深夜労働時間帯終了時刻の29:00(翌朝5:00)よりも大きければ、29:00(翌朝5:00)となり、小さければ休憩終了時刻となります。

深夜労働対象時間帯の休憩開始時刻を判断する

MIN(MAX(“22:00”,D12),G12)

まず、MAX関数の戻り値ですが、休憩開始時刻(D12)が深夜労働時間帯開始時刻の22:00よりも小さければ、22:00となり、大きければ休憩開始時刻となります。次にMIN関数の戻り値ですが、MAX関数の戻り値である深夜時間帯勤務開始時刻が休憩終了時刻よりも小さければ休憩終了時刻が深夜帯休憩開始時刻となり、大きければMAX関数の戻り値が深夜帯休憩開始時刻となります。

深夜労働時間帯の休憩終了時刻と休憩開始時刻から深夜労働時間帯の休憩時間を算出する

上で求めた深夜帯休憩終了時刻MIN(“29:00”,G12)から深夜帯休憩開始時刻MIN(MAX(“22:00”,D12),G12)を減算した値が深夜帯休憩時間となります。

深夜労働対象時間を計算する

最終的には早出における深夜労働対象時間を加算した深夜労働時間から深夜帯休憩時間を減算した値が、深夜労働対象時間となります。

タイトルとURLをコピーしました