エクセル 深夜勤務時間の算出方法

質問

こんにちは。

今、会社でエクセルを使った勤務表を作成しています。
出社時間、退社時間をそれぞれセルに入力すると、深夜勤務時間のセルに夜22：00から朝5：00の間に勤務した時間を抽出して表示したいと思っています。

例として、出社時間を「20：00」、退社時間を「3：00」と入力した場合、深夜勤務時間に「5：00」という風に計算して表示させたいのです。

深夜勤務時間にはどういった計算式をいれればいいでしょうか？
よろしくお願い致します。

回答No.4

wonder99 · ベストアンサー

昔私も、タイムカードの集計と手書きの集計を合わせる為に
エクセルで表を作った事がありますが・・・・
タイムレコーダーが特殊な計算方法を取っていた事と
勤務時間帯が昼夜問わずだった事、深夜勤務時間帯は手当がつくので分ける事、
昼休みは基本的に1時間引く事になっているが、昼休みを取らないで
働いたりする事があった事などなど・・・諸事情により
もっともっと面倒臭い事になりました（苦笑）。

IF関数による場合分けが必要です。
(1) 出社時間、退社時間ともに深夜にかからない場合
(2) 出社時間が深夜にかからないが、退社時間が深夜にかかる場合
(3) 出社時間が深夜にかからないが、退社時間が深夜を超えて（5時以降）いる場合
(4) 出社時間、退社時間ともに深夜時間帯の場合
(5) 出社時間が深夜で、退社時間が深夜を超えて（5時以降22時以前）いる場合
(6) 出社時間が深夜で、退社時間が昼間時間を超えて（22時以降）いる場合

つまり、深夜勤務時間は
(1)なら【0】であり、
(2)なら退社が23:59までは【退－出】、0:00以降なら【退+24:00－出】、
(3)なら【7】、
(4)なら出社が23:59までの場合、
退社が23:59までは【退－出】、0:00以降なら【退+24:00－出】、
出社が0:00以降の場合は【退－出】、
(5)なら出社が23:59までの場合、【29:00－出】、
出社が0:00以降の場合は【5:00－出】
(6)なら合算
といった感じです。どうです、面倒臭いでしょう・・・。
（というか、場合分けが多過ぎてIFの7段の限界を超えているような・・・。）
私は、昼間の勤務時間をIF関数6段重ねで算出し、深夜の時間帯は
総勤務時間から昼間の時間を引くような形にしてました。

ですから、やはり他の方がおっしゃるように27：00と入力するのが
一番面倒がないように思いますよ。
入力時に3：00を27：00と計算するのが面倒臭いというのであれば、
出社時間＞退社時間なら退社時間に24時間足すセルを用意して
入力セル（「3：00」と入力する）と
退社時間の結果表示セル（「27：00」と表示させる）を分け、
退社時間は結果表示セルから引っ張るようにすれば良いように思います。

お礼

皆様たくさんの回答ありがとうございました。
失礼かとは思いますがここでまとめてお礼を申し上げます。

ＩＦ関数を組み合わせてなんとかできました！！
みなさんがおっしゃるように、退社時間を一旦「27：00」に変更することで落ち着きました。

回答No.3

shino0413

下記のような条件付けしてはどうでしょう。
　退社時間－22:00>0:00　,
　　であれば（退社時間－29:00>0:00　,であれば「7:00」,それ以外は「退社時間－22:00」）,
　それ以外は「0:00」

パパッと書いたので間違ってるかもしれませんけど、要はIFを何段か使うということです。
上の場合はAM8:00～翌AM8:00みたいな場合の考え方ですけど、始業がAM3:00からとか２度の
深夜時間にまたがるなら合算、22:00以降に始まるとかなら出社時間についても同様の条件付け
で複合させると出来ると思います。
出社時間、退社時間がそれぞれどこの時間帯にあるかによって、何から何を差引けば良いかを
考えればOKでしょう。

退社時間は他の方が書いている通り、27:00とした方が間違いがないです。（いらない処理が増えるだけなので）

回答No.2

sakemoni1008

退社時間に２７:００と入力すれば良いです。
表示上は、３:００となりますし、計算も退社時間から深夜開始時間を引いて下さい。
深夜勤務時間が５:００と表示されますよ～
但し、金額計算時は深夜勤務時間Ｘ深夜勤務単価では正しく計算しません。
深夜勤務時間Ｘ深夜勤務単価Ｘ２４にして下さい。

回答No.1

noname#17648

アドバイス

退社時間を27：00　とすると、簡単に出来ます。
3：00　だと、
3時のとこか、27時のことはの判断が難しいのです。
（もしかして、この判断方法に関する質問でしょうか？）

補足

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

総勤務時間は下記のように、

=IF(退社時間<出社時間,(退社時間+"24:00")-出社時間,退社時間-出社時間)

で0時をまたぐ計算ができたのですが、勤務した時間の中に22：00から5：00までの時間がどれだけ入っているか抽出したいと思ったのです。
出社時間と退社時間をそれぞれ分けて計算させないといけないのでしょうか？