『所定労働時間を超過した場合の集計方法について』（watact）
集計方法についてご教示いただけますと幸いです。
24時間シフトの稼働を管理する際に、下記の●表1を手動で入力し、
そのデータを元に●集計結果にある1，から8，をExcelの数式で求めたいと考えております。
始まりが通常時間、深夜時間からとバラバラとなり、
中には深夜時間の関係ないシフトも混在しております。
また、表1の最下段の場合、
5：2時間
6：3時間
7：6時間
8：0時間
と労務管理上は計算しますが、そのようにカウントする手法に辿り着けず、
有識者の皆様にご助力賜りたく存じます。
●表1
A列/ B列/ C列/ D列
出勤時刻/ 退勤時刻/ 休憩入り時刻/ 休憩戻り時刻
9:00/ 2:00/ 23/ 24
23:00/ 9:00/ 2/ 3
8:00/ 19:00/ 13/ 14
22:00/ 9:00/ 21/ 22
21:00/ 9:00/ 23/ 24
●集計結果
1,拘束時間：E列
2,深夜開始：F列
3,深夜終了：G列
4,通常合計：H列
5,通常 ：I列
6,通常残業：J列
7,深夜時間：K列
8,深夜残業：L列
2，と3，は勤務時間が深夜時間で、計算が必要な場合に、
2：22：00、3：29：00と表示させるようにしたいと考えております。
上記以外の列を追加し、計算用として活用いただく形でも全く問題ございません。
ご教示の程、何卒よろしくお願いいたします。
＜ 使用 Excel：Excel2013、使用 OS：Windows11 ＞
|[A] |[B] |[C] |[D] |[E] |[F] |[G] |[H] |[I] |[J] |[K] |[L]
[1]|出勤時刻|退勤時刻|休憩入り時刻|休憩戻り時刻|拘束時間|深夜開始|深夜終了|通常合計|通常|通常残業|深夜時間|深夜残業
[2]|9:00 |2:00 | 23| 24| | | | | | | |
[3]|23:00 |9:00 | 2| 3| | | | | | | |
[4]|8:00 |19:00 | 13| 14| | | | | | | |
[5]|22:00 |9:00 | 21| 22| | | | | | | |
[6]|21:00 |9:00 | 23| 24| | | | | | | |
通常、深夜の時間帯はどうなっていますか。
休息はなぜ時間表示にしないのですか。
（MMKK） 2025/08/25(月) 21:49:03
表のお手伝い、誠にありがとうございます。
下記に現状で得られている結果を反映させていただきました。
[3][5][6]の深夜残業をカウントしないのが正しい結果ですが、
そちらを通常残業にカウントする方法に難儀しております。
休憩時間は、新たに30分単位での取得が要件に加わりますので、
是非、時間表示で記入した場合に対応できるようにしたく存じます。
引き続き、お時間ある際にご支援いただけますと幸いです。
|[A] |[B] |[C] |[D] |[E] |[F] |[G] |[H] |[I] |[J] |[K] |[L]
[1]|出勤時刻|退勤時刻|休憩入り時刻|休憩戻り時刻|拘束時間|深夜開始|深夜終了|通常合計|通常|通常残業|深夜時間|深夜残業
[2]|9:00 |2:00 | 23| 24| 17| 22| 29| 13| 8| 5| 3| 3
[3]|23:00 |9:00 | 2| 3| 10| 22| 29| 4| 4| 0| 5| 1
[4]|8:00 |19:00 | 13| 14| 11| #VALUE!| null| 11| 8| 3| 0| 0
[5]|22:00 |9:00 | 21| 22| 11| 22| 29| 4| 4| 0| 6| 2
[6]|21:00 |9:00 | 23| 24| 12| 22| 29| 5| 5| 0| 6| 3
E: =IF(OR(ISBLANK(N9),ISBLANK(O9)),"",(O9-N9)*24)
F: =IF(K9=0,"",S9)*24
G: =IF(K9=0,"",29)
H: =IF(OR(ISBLANK(N9),ISBLANK(O9)),"",MAX(0,(E9-R9)-K9))
I: =H9-J9
J: =IF(OR(ISBLANK(N9),ISBLANK(O9)),"",MAX(0,(E9-R9)-8-MAX(0,K9-MAX(0,8-H9))))
K: =IF(OR(ISBLANK(N9),ISBLANK(O9)),"",MAX(0,U9-V9))
L: =IF(OR(ISBLANK(N9),ISBLANK(O9)),"",MAX(0,K9-MAX(0,8-H9)))
下記は作業として別列に数式入力しております。
N: =IF(ISBLANK(A9),"",A9)
O: =IF(OR(ISBLANK(A9),ISBLANK(B9)),"",B9+IF(B9<N9,1,0))
P: =IF(OR(ISBLANK(C9),ISBLANK(D9)),"",TIME(C9,0,0)+IF(TIME(C9,0,0)<N9,1,0))
Q: =IF(ISBLANK(D9),0,TIME(D9,0,0)+IF(TIME(D9,0,0)<=P9,1,0))
R: =IF(OR(ISBLANK(P9),ISBLANK(Q9)),0,MAX(0,(MIN(O9,Q9)-MAX(N9,P9))*24))
S: =IF(ISBLANK(N9),"",IF(HOUR(N9)>=22,TIME(22,0,0),IF(HOUR(N9)<5,TIME(22,0,0)-1,TIME(22,0,0))))
T: =IF(ISBLANK(S9),"",S9+TIME(7,0,0))
U: =IF(OR(ISBLANK(N9),ISBLANK(O9)),0,MAX(0,(MIN(O9,T9)-MAX(N9,S9))*24))
V: =IF(OR(ISBLANK(P9),ISBLANK(Q9),ISBLANK(S9),ISBLANK(T9)),0,MAX(0,(MIN(T9,Q9)-MAX(S9,P9))*24))
（watact） 2025/08/26(火) 08:49:05
１．正解図(＝あるべき姿）を書いていただけないですか？
２．[5]行目について、休憩が21〜22なのに、出勤時刻が22時というのが腑に落ちないのですが、 実際の休憩時刻ではないと云うことなんですか？ それとも出勤前に休憩を取った(？)と云うことなんですか？
他の回答者のレスがついたら、この私の書き込みは無視していただいて結構です。
（半平太） 2025/08/26(火) 09:58:40
1. 正解図を記載させていただきます。
|[A] |[B] |[C] |[D] |[E] |[F] |[G] |[H] |[I] |[J] |[K] |[L]
[1]|出勤時刻|退勤時刻|休憩入り時刻|休憩戻り時刻|拘束時間|深夜開始|深夜終了|通常合計|通常|通常残業|深夜時間|深夜残業
[2]|9:00 |2:00 | 23| 24| 17| 22| 29| 13| 8| 5| 3| 3
[3]|23:00 |9:00 | 2| 3| 10| 22| 29| 5| 4| 1| 5| 0
[4]|8:00 |19:00 | 13| 14| 11| | | 10| 8| 2| 0| 0
[5]|22:00 |9:00 | 21| 22| 11| 22| 29| 4| 1| 3| 7| 0
[6]|21:00 |9:00 | 23| 24| 12| 22| 29| 5| 2| 3| 6| 0
2. 誤った休憩時間を入力した際に除外（計算されない）されるように入力した実態にはない休憩時間となります。
改めてご確認よろしくお願いいたします。
（watact） 2025/08/26(火) 16:33:53
誤った休憩時間データもある前提なのですね。
色んなパターンの誤りがありそうな気がしますが・・
どんなパターンの誤りだと、どんな除外措置にするべきなのか、 私には皆目見当が付かないので降ります m(__)m
（半平太） 2025/08/26(火) 20:41:02
思い付きですが、 他のシートに0:00から30分刻みの開始時間で30分刻みで24時間分のマトリックスデータを準備すれば、 それぞれの入力を入力規則でリスト化できそう。 また、計算にも利用できそう。 Matchで列、行を検索して、セル数*0.5hで時間は出せるかな。
有識者の方々いかがでしょう？ （tkit） 2025/08/27(水) 08:48:42
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.