エクセルの時間外勤務時間と深夜時間を自動割り振りできる計算式を教えてください

AさんBさんCさんにそれぞれ勤務時間が割り当てられています

Aさん　20:30～0:00
Bさん　 0:00～3:00
Cさん　 3:00～6:30
※20:30以前、6:30以降は3人とも勤務時間になります

深夜勤務時間(150/100)は22時～５時
Aさんの時間外対象(125/100)は０時～6時30分
Bさんの時間外対象(125/100)は21時～0時　　3時～6時30分
Cさんの時間外対象(125/100)は20時30分～3時

①　3人に20:45～1:10まで仕事をしてもったパターン
⓶　3人に2:50～5:15まで仕事をしてもったパターン

黄色いセルに手動で入力すると緑のセルに自動入力される計算式をご教示ください
よろしくお願いします

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

• ありがとうございます。
  
  日付をまたぐパターンと、またがないパターンが混在する場合の計算式がうまくいかずに悩んでいるところです。

  No.1の回答に寄せられた補足コメントです。 補足日時：2018/05/23 12:36

No.3 回答者： yoreyore 回答日時： 2018/05/27 15:45

補足です。

１）日付をまたぐパターンと、またがないパターンが混在する場合の計算式がうまくいかずに悩んでいるところです。
⇒　式か値が、《日付け》ではないものを扱っているためだと思います。
セルに、ただ｢8:30｣｢21:00｣とただ入力すると、日付のない時刻になります。（内部では1未満の数になっています）そのままでは、｢午前零時をまたいで翌日｣は扱えません。
｢8:30｣｢21:00｣と入力されているセルのデータを、強制的に日付のあるデータに置き換える式を使って、別のセルに《日付付き時刻データ》として表示させて、その《日付付き時刻データ》を使うようにすれば、問題は解決すると思います。
例：｛[A2]　20:45　[B2]　01:10　｝の場合、
[A8]　=DATE(2010,5,1)+A2 [B8] =IF(B2<A2,DATE(2010,5,2)+B2,DATE(2010,5,1)+B2)
としておくと、画面上は、[A8]と[A2]、[B8] と[B2]は同じように見えるかもしれませんが、
エクセルのセルにあるデータの値としては、開始時刻は5月1日の時刻、終了時刻は5月1日か5月2日かの時刻になっています。　その[A8]と[B8]とを使えば、時間の引き算もできます。
なお、[A2][B2]だけでなく、計算や比較の対象にする時刻はすべて、日付付きの時刻にしないと、結果がめちゃくちゃになります。

　　～～～～　　　～～～～～　　　～～～～～～
２）3人ともAM8:30から翌日のAM8:30までの勤務となり、夜間の休憩時間がそれぞれバラバラの時間帯に、合計6:30取ってもらいます。
⇒　そういう状態だと、2行目、11行目に入力している｢仕事をしてもらっている時間の始めの時刻と終わりの時刻｣はなんなのでしょう。
⇒深夜勤務時間は22時～５時の間に行われた勤務というのは法的なものです。｢時間外対象は、21時～0時、3時～6時30分｣という決めをしても、0時～3時は、時間外対象ではなくても、深夜勤務としての加算が必要です。

３）休日の扱いは、深夜勤務の途中で日付が変わる場合には、別の扱いが必要です。つまり、①平日と平日、②平日と休日、③休日と平日、④休日と休日　の4種類があるので、仮に、22:00~02:00の合計4時間勤務でも、賃金額は変わります。　つまり、｢3人に22:00~02:00で仕事をしてもらいます｣では、条件が不足です。

４）勤務体制・シフトと所定労働時間、残業(所定労働時間以外の勤務)の関係がわかりません。
20:30～06:30は、誰か一人は勤務中になるようなシフトで、実際には
AさんBさんCさんに勤務時間を割り当てている
20:30～0:00(Aさん)、 0:00～3:00(Bさん)、　3:00～6:30(Cさん)
さらに、

０時～6時30分　Aさんの時間外対象時間帯
(Aさん)の
08:30～20:30は所定内勤務（？そんな長時間はあり得ない）
20:30～0:00は所定内勤務で、
22:00～00:00は所定内勤務で深夜勤務
00:00～05:00は時間外対象時間帯で深夜勤務
05:00～06:30は時間外対象時間帯
06:30~08:30は所定内勤務

21時～0時　　3時～6時30分　Bさんの時間外対象時間帯
(Bさん)の
08:30～21:00は所定内勤務（？そんな長時間はあり得ない）
21:00～22:00は時間外対象時間帯
22:00～00:00は時間外対象時間帯で深夜勤務、
00:00～03:00は所定内勤務で深夜勤務
03:00～05:00は時間外対象時間帯で深夜勤務
05:00～08:30は所定内勤務

Cさんの時間外対象(125/100)は20時30分～3時　
　　省略

わからないです。

No.2 回答者： yoreyore 回答日時： 2018/05/25 19:03

意味がよくわかりません。

私の理解では、《午後１０時から翌午前５時までの労働は｢深夜労働｣扱い、｢深夜労働｣には基礎賃金の25%増し以上の加算が必要》《時間外労働をした場合には、基礎賃金の25％増し以上の加算を、時間外労働が深夜労働の場合には基礎賃金50%増し以上の加算を、労働が休日労働に当たる場合には基礎賃金の35％増し以上の加算》だったと思います。　基礎賃金が何かが問題です。　『※20:30以前、6:30以降は3人とも勤務時間になります』ということは、3人とも、6:30～20:30の中に平常勤務時間帯があって、その平常勤務時間帯の時間賃金を基礎賃金の100%と見るのだと思います。

①＞　勤務時間が割り当て　　Cさん　 3:00～6:30　
②＞　Cさんの時間外対象(125/100)は20時30分～3時
③＞　※20:30以前、6:30以降は3人とも勤務時間になります

①と②の関係がわかりません。
①と③の関係がわかりません。

Cさんは、勤務が3:00～20:30の(17:30)③ならば、
『時間内勤務で、時間外加算はなし』ということはないでしょう。

Cさんが、2:50～5:15に勤務すれば、法的には、2:50～5:00の2:10は、所定勤務時間内でも｢深夜労働｣の基礎賃金の25%増し以上の加算が必要だと思います。

《時刻と時間帯ごとの、三人の割り増し後の基礎賃金に対する時間賃金比率がはっきり》すれば、働いた時刻（開始時刻・終了時刻）が決まれば、そのまま計算できると思います。
《時刻と時間帯ごとの、三人の割り増し後の基礎賃金に対する時間賃金比率がはっきり》しているのですから、勤務の開始時刻と終了時刻の途中に｢基礎賃金に対する時間賃金比率の変更になる時刻(05:00、22:00あるいは、時間外になる時刻、時間外ではなくなる時刻)がある場合｣、それを分割点にして行くだけです。
働いた時刻（開始時刻・終了時刻）が（終了時刻<開始時刻）の場合は、途中に0:00があるものとみなし、日をまたぐ（2日間に分けて）処理するだけだと思います。

この回答へのお礼

ありがとうございます

勤務時間に関して詳しく記載しませんでしたが、3人ともAM8:30から翌日のAM8:30までの勤務となり、夜間の休憩時間がそれぞれバラバラの時間帯に、合計6:30取ってもらいます

お礼日時：2018/05/26 06:54

No.1 回答者： mike_g 回答日時： 2018/05/23 07:43

見事な添付図と説明で、アッサリと「…をご教示ください」と仰る貴方自身は、「ご教示」されなくても何処までならお出来になるのでせうか？

だけですか？