エクセルで変則労働時間制の法定内・外労働時間の算出を自動で行いたい

初心者です。エクセルで変則労働時間制を導入している場合での、タイムカード集計一覧を作るように言われましたが全くわかりません。どなたかご指導頂ければと思います。

条件：変則労働時間制
　1週間（日曜日から起算-土曜終わり）で41時間以上48時間までは法定外残業へカウント（40H超の時点で累計で表示）、49時間以上56時間までは法定内残業に時間累計を表示これを週ごとに繰り返す。又、月末が日曜以外は次月に、各法定外、内時間を計算するための累計時間を反映させたい。

私自身が営業畑から人事に移動していきたばかりで、内容が分からないかもしれませんが、その場でも上記の条件で自動計算ができるエクセルの関数をご教授頂ければ幸いです。

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

• 補足とイメージのエクセルを添付しました。
  実際に使用を考えている一覧です。
  
  月を変更すると日付(曜日も）自動で変更、日曜日起算土曜迄を１週間とするを基本に
  表に付け足しして管理希望。（表は累計をいれてませんが入れて管理したい）
  
  休日は土日ときまっていません。
  また、深夜をまたぐ可能性もあります。

  
    補足日時：2019/11/08 14:12

• 何度も回答頂き本当にたすかっています。
  おっしゃる通り、ピンクの部分は手入力の個所です。
  この表すごく見やすいです！「通算計」は1週間の累計をとりあえず表示した方がわかりやすいのかと思い入れたのですが、頂いた表のほうが見やすいのでこの形で管理したいです。
  セルの融合解除ごもっともでした(お恥ずかしいばかりです）
  
  出来れば頂いたこの表、計算式を教えていただきたいです！
  数日家でも考えていましたが、私のスキルでは到底むりでした。

  No.7の回答に寄せられた補足コメントです。 補足日時：2019/11/12 14:51

• ダウンロードしました！
  
  本日の午後からこの業務時間に入れそうなので、実際使ってみます！
  ざっとみててですが、U12の計算式（法定内残業）は、週累計のF13からW12（通常の40時間）を引き残り時間を表示ですが、法定内残業が49H～56H（8H）、それ以降は超過残業(言い方が不明：上司からの指示書に記してあったので。。）を出したい場合は、VとWの間にその計算式を入れたいです。
  その場合は法定外、法定内に既に入っている計算式も変更になりますか？
  その他、午後より新たに、わからない点が出たら質問させて頂きたいと思います。1

  No.12の回答に寄せられた補足コメントです。 補足日時：2019/11/13 10:10

• 一つ私の勘違いがあり、修正方法でも構わないので教えて頂きたいです。
  
  条件の変更
  1.日をまたぐときでも、始業日へ反映させる。
  　例）1日　始8：00~終25：00　休憩1H　/通常　16H　8H超え手当　8H　深夜手当　3H
  2.深夜手当は22時~翌5：00に対してつく。遅番の場合、始業時間が21：00～終了が翌日7：00等の場　合は翌日5時～7時の2Hは通常へ反映

  No.15の回答に寄せられた補足コメントです。 補足日時：2019/11/15 10:44

• 回答ありがとうございます。
  確認したところ⑤の特別なルールがありました。
  遅番は夜勤勤務(介護関係です）となります。休憩＝1Hではなく出勤後、利用者様が寝静まったと同時に、一旦就寝します。そのため深夜5時間ないし6時間は休憩扱いになります。ないしなのは、その判断は従業員の申告で反映するためです。かなり特殊です。可能であれば休憩時間を通常から引く場合、深夜から引く場合の入力欄があるとどちらでも対応可能なのかと（双方とも手入力）。
  ややこしくて申し訳ないです。
  やり取りに感謝です。

  No.16の回答に寄せられた補足コメントです。 補足日時：2019/11/15 17:04

• 午前1時は25：00と入力します。
  1日の休憩を含めた時間が24時間を超えないというのが、私の読解力では理解ができず。。
  すみません。

  No.19の回答に寄せられた補足コメントです。 補足日時：2019/11/19 14:24

• ご説明ありがとうございます！
  そうであれば、「24時間を超えない」です。
  昨日補足送信したのですが、ネット環境が不安定で送信ができていなかったようです。
  又、数日前からもネット環境が不安定だったため、最終でいただいたものがダウンロード期限
  がすぎてしまいました。申し訳ないのですが再アップお願いできれば幸いです。

  No.20の回答に寄せられた補足コメントです。 補足日時：2019/11/21 13:00

No.21 ベストアンサー 回答者： GooUserラック 回答日時： 2019/11/21 17:38

修正版をアップしました。

2019/11/24 17:33:57 までダウンロード可能です。
ダウンロードＵＲＬは「 http://dtbn.jp/c41ehHz 」です。

この回答へのお礼

GooUserラックさん、何度もご回答頂きありがとうございました♪
ダウンロードし使用させて頂いておりますが、とても使い勝手がいいです！

お礼日時：2019/11/22 09:21

No.20 回答者： GooUserラック 回答日時： 2019/11/19 14:51

No.19 の補足

例えば翌日の１３時って何時と入力しないといけなかったっけ？と考えるのが面倒ではないかなぁ？と思っただけです。
「１日の休憩を含めた時間が２４時間を超えない」は具体的に言うと「午前９時始業だった場合、翌日の午前９時前には必ず退社しているか？」という事です。翌日の午前９時前には必ず退社していれば「２４時間を超えない」、翌日の午前９時を超えてしまう事が有れば「２４時間を超える事もある」という事になります。
「２４時間を超えない」場合は、終業時刻が始業時刻より小さければ終業時刻は翌日なんだと判断出来るので単純に２４時間を足して計算すれば良いのだけど「２４時間を超える事もある」場合は何回２４時間を超えたか判断出来なくなるので２４時間以上の表示か必要になります。

No.19 回答者： GooUserラック 回答日時： 2019/11/15 23:49

今更かもしれませんが、もしかしたら「午前１時」を「25:00」でなく「1:00」のような入力をされたいでしょうか？

１日の休憩を含めた時間が２４時間を超えないというなら
終業時刻を計算に使う前に「終業時刻=IF(始業時刻>終業時刻,終業時刻+1,終業時刻」みたいな事をすれば可能です。いかがいたしますか？

No.18 回答者： GooUserラック 回答日時： 2019/11/15 19:43

修正版をアップしました。

翌日への持越しが無くなったのでシンプルになりましたが、深夜残業が範囲指定になった為そちらは複雑になってしまったかもしれません。

ダウンロードのＵＲＬは「 http://dtbn.jp/a0gbrua 」です
2019/11/18 19:31:15 までダウンロード可能です。

No.17 回答者： GooUserラック 回答日時： 2019/11/15 17:14

それでは深夜休憩の欄を追加してみます。

No.16 回答者： GooUserラック 回答日時： 2019/11/15 13:48

遅番の場合「始21:00~終31:00 休憩1H」の場合の休憩はどのように考えるのでしょうか？

① なるべく通常時間で休憩を取った事にする
② 通常時間と深夜時間との割合が多い方でまとめて休憩を取った事にする
③ 通常時間と深夜時間との割合で休憩時間を分けて取った事にする
④ 通常時間が特定の時間を超えた時に通常時間で休憩を取った事にする（特定時間を提示して下さい）
⑤ その他、特別なルールが有る（詳しく提示して下さい）
※ たしか８時間ごとに１時間の休憩を取るようになっていた気がします。この辺も詳しく提示して下さい。

No.15 回答者： GooUserラック 回答日時： 2019/11/13 19:51

集計の欄が横に長くなってしまったので表の下に置くバージョンを作成しました。

・いくつかの修正も行っています。
・印刷範囲も設定しましたのでＡ4縦でうまく収まると思います。
イメージは下図のような感じで印刷範囲は赤枠で囲まれた所になります。

ダウンロードのＵＲＬは「 http://dtbn.jp/oTXZa3a 」です

No.14 回答者： GooUserラック 回答日時： 2019/11/13 11:10

修正版を公開しました。

2019/11/16 11:06:44 までダウンロード可能です。

ダウンロードＵＲＬは「 http://dtbn.jp/hgxQ2Za 」です

No.13 回答者： GooUserラック 回答日時： 2019/11/13 10:47

とりあえず現在の仕様は以下のようになっています。

　（「*」が有る項目はこちらで勝手に解釈している可能性が高いです）

・日曜日０時開始、土曜日２４時終了で集計
・２４時を過ぎたものは翌日扱い
・前月に一部被った場合は今月扱い
・翌月に一部被った場合は翌月扱い
・週４０時間越え４８時間以下は法定外として集計
・週４８時間越えは法定内として集計（* ５６時間以降もここに集計されます）
・２２時以降は深夜手当が付きます（* 翌日以降になった分は全て対象になります）
・１日の就業時間が８時間を超えた分には８時間越え手当が付きます（*）
・１日複数回の就業には対応していません（前日からの就業とは別扱いになります）


No.12 に対する補足コメントついて

・週５６時間越えは新たに超過残業の欄を追加すれば良いのでしょうか？
　（法定内残業を２つに分ける事になり、図のように変更予定です）

※ 予定と違っている物や追加事項が有りましたらご提示ください。出来る範囲で対応してみます。

No.12 回答者： GooUserラック 回答日時： 2019/11/12 21:56

No.11 の補足

ダウンロードのＵＲＬを書き忘れました。
http://dtbn.jp/PMmq4Fa