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

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

条件：変則労働時間制
　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.11 回答者： GooUserラック 回答日時： 2019/11/12 21:54

計算式を細かく指示するのはとても大変なのでファイルを公開しました。

2019年11月15日 21:35まではダウンロード可能です。

☆ 使い方
・Ａ１：Ｂ１セルに西暦で年を数字のみで入力します。今年なら「2019」です
・Ｃ１：Ｄ１セルには対象の月を数字のみで入力します。来月なら「12」です
・黄緑の部分のみ入力出来るようにシート保護をかけてありますが、保護解除はパスワード無で出来ます。
・Ｙ～ＡＣ列は作業用に使用しています。運用時には非表示にするなどが良いと思います。
・思い違いをしている所が多々あると思いますので指摘して下さい。
・時刻の入力で２４時を超えるものは２４以上の時間で入力して下さい（翌日の午前１時なら「25:00」と入力して下さい）
・２４時を超えた分は全て翌日扱いになります。

No.10 回答者： GooUserラック 回答日時： 2019/11/12 19:33

「残業単価」（字が良く見えないので違うかもしれません）というものが有りますが、

これは１日の就業時間が８時間を超えた分に付く法定外単価や法定内単価とは別につく１時間当たりの手当ですか？（連続長時間労働に対する深夜手当のようなものでしょうか？）
それとも法定外単価や法定内単価に含まれてしまうので不要な項目でしょうか？

No.9 回答者： GooUserラック 回答日時： 2019/11/12 15:55

No.8 のレイアウトで考えてみます。

通常の欄ですが次のどれになりますか？
① 定時時間（例えば９時～１８時）から休憩時間を引いた時間
② ８時間以内の就業時間
③ 深夜時間にならない分の就業時間で週単位累計で４０時間以内の時間
　（例えば、毎日１１時間だったら、３日目までは１１時間、４日目は７時間、その後０時間）
④ その他（具体的な方法を提示して下さい）

No.8 回答者： GooUserラック 回答日時： 2019/11/12 15:08

No.7 の修正

「深夜をまたぐ可能性もあります。」となっているので前月の最終日の２４時を超えた分を反映しないといけないので図のようにする必要がありそうです。

No.7 回答者： GooUserラック 回答日時： 2019/11/10 07:12

レイアウト考えてみました。

ご参考にどうぞ！
※ 図がなるべく縮小されないで位置関係が判るように幅を縮めてあります。

なお、「通常」の所に有った「通算計」は何だか判らなかったのでとりあえず省いて、週内の「通算時間」の欄を作成しました。あと意味が無さそうなセル結合も解除しました。

No.6 回答者： GooUserラック 回答日時： 2019/11/10 06:15

よく考えてみると手入力または他のデータからコピーされる場所は図のピンクの部分ですよね？

しかも週途中の「時間外」「時間内」の欄は不要ですよね？
もう一度レイアウトを考え直された方が良いと思います。

以下気づいた点
・週数は４週か５週になる。
・間に週計を持ってくると計算式がとても面倒になる。
・入力箇所はまとめた方が間違いがない

　⇒ 表の右側に「時間外」「時間内」「その他（週内のその他の合計）」の週ごとの表を作られた方が良いと思います。別回答として再度考えたものを後日あげてみます。

No.5 回答者： GooUserラック 回答日時： 2019/11/08 19:26

週計を内部に入れたものを作成してみました。

手入力される所はピンクの部分でしょうか？
頂いた図で良く見えなかった部分を適当にしました。抜けていたり違っている場所を指摘していただけると他の方も判りやすいかもしれません。

No.4 回答者： GooUserラック 回答日時： 2019/11/08 15:21

① 図の中のコメントですがよく見えないのですが「2019年」「2月度」を入力したら、自動で「日付」「曜日」の一覧表を作成して計算式を残してクリアするという事で宜しいですか？

②「2019年」の欄は「2019」が入力されていて書式設定で「2019年」と表示させているのでしょうか？（数式バーにはなんと表示されていますか？）
③「2月度」の欄は「2」が入力されていて書式設定で「2月度」と表示させているのでしょうか？（数式バーにはなんと表示されていますか？）
④ 前回の説明では月末は翌月へとなっているので「1/27」～「2/23」を作成するのでしょうか？
⑤ 始業時間は端数を消して入力されていますか？消されていない場合の処理はどうするのですか？
⑥ 週の集計はどこに置けばよいのですか？表の中の週が変わったところでしょうか？それともこの表の右側に新たに作るのでしょうか？
⑦ 上のピンクや青の欄には何が入っていますか？
⑧ 手入力または何も行わない部分と計算式を入れる部分を色分けして再度掲載して下さい。

No.3 回答者： GooUserラック 回答日時： 2019/11/06 14:39

No.1 の補足など

イメージとしては図のような感じかと思いますが、実際の配置が判らないと具体的な回答は無理なので仮想データを入れその仮想データに対しての想定結果も提示していただけると間違いないです。

今回の仮想データは次の様な物を想定しています。（かなり特殊です）
・通常９時～１８時（休憩１時間）
・土曜・日曜・祝日休み
・休日手当の様な物は今回考慮不要
・緊急で退社後も再出勤する事もある
・緊急で数日徹夜する事もある

以降質問です
① 休憩時間は手入力で良いですか？自動計算希望なら計算方法を教えて下さい。
② 休日の考慮は不要で間違いないですか？
③ １日に数度出社する可能性はありますか？
④ ２４時間を超えるような労働が発生する事はありますか？
⑤ 週５６時間以上残業をする可能性はありますか？
⑥ 定時という概念は不要で良いですか？
⑦ 図のようにデータは秒単位だけど表示分単位で計算した方が良いですよね？

No.2 回答者： 銀鱗 回答日時： 2019/11/06 14:19

まず、

　1週間の労働時間の合計時間を求める。
次に、
　41時間以上48時間までの時間を法定外残業の欄にさせるためにMIN関数とMAX関数を使って引き算をする。
　＝MIN(48:00,合計時間)-MAX(40:00,合計時間)
更に、
　49時間を超える分を同様にして法定内残業を求める。

営業畑云々と泣き言いわずがんばれ。
あと、実際の数字の取り扱いは専任者に確認をするようにしましょう。
マジでヤバい表現してるぜ。

この回答へのお礼

回答ありがとうございました。
説明付きなのでわかり易く、応用させて頂いてます。

お礼日時：2019/11/15 10:25