深夜勤務を含む時間外割増賃金計算シート

質問

24時間稼動の工場で働く人の割増賃金を計算するためのタイムシートを作成中です。次のように進んでいます。尚、深夜残業時間帯は午後10時から翌朝5時までとしています。

Ｅ列：出社時刻
Ｆ列：退社時刻
Ｇ列：就労時間合計（休憩１Ｈのぞく）
＝IF(ISNUMBER(E8),F8-E8-TIME(1,0,0),"")
Ｈ列：残業時間（8時間を超えた分、25%割増）
＝IF(OR(E8="",F8=""),"",MAX(MIN(F8+(F8<E8),22/24)-E8-18/48,0))
Ｉ列：深夜労働１　（休日以外、25％割増）
＝IF(OR(E9="",F9=""),"",MAX(F9(F9<E9),22/24)-22/24)
Ｊ列：深夜労働２（休日以外で8時間を超えた部分、50%割増）
＊８Ｈを超える残業時間が深夜時間帯にかかる部分
Ｋ列：休日労働１　（35％割増）
＝IF(OR(E9="",F9=""),"",MIN(F9+(F9<E9),22/24)-E9-1/24)
Ｌ列：休日労働２　（深夜労働時間帯にかかる部分、60％割増）

Ｊ列とＬ列にどのように式を入れたらよいのかわからないのですが、現状だと他の列の式も変える必要がありそうです。というか全部変える必要あり？
極端な例ですが、普通の日に10時出社、翌朝6時退社した場合、Ｈ列４Ｈ、
Ｉ列７Ｈとなるべきですが、現状だとＨ列３Ｈ，Ｉ列８Ｈと出てしまいます。

Ｊ列、Ｌ列の式を含めて、どのように修整したらよいのか、教えてください。。どうぞ宜しくお願い致します。何だかこれを作るのに疲れてしまって、いいフリーソフトが無いか探してみましたが、見つかりませんでした。。

回答No.2

noname#204879

[ANo.1この回答への補足]に対するコメント、

》 現在はＢ列に日付、Ｃ列に曜日、Ｄ列に有給か欠勤かの区別を入れていて…

そういうことは、出し惜しみせずに最初から仰るべきです。
日付があれば、それに対する曜日は一義的に分かるので、計算上は曜日の列は不要になります。あっても構いませんが。
「出社日」が既にあることは分かりましたが、計算を簡単にするために「退社日」も追加したら如何ですか？

それから「割増賃金の対象となる」休日は、日曜日以外にはないのですか？祝日とか年末年始休暇とか創立記念日とか…

補足

そうですね、失礼致しました。。

計算が簡単になるのなら「退社日」は出社時刻と退社時刻の間に入れようと思います。

尚、「割増賃金の対象となる」休日は日曜日のみです。年末年始も祝日も関係無しです。

回答No.1

noname#204879

「出社時刻」「退社時刻」のそれぞれの左列に「出社日」「退社日」を設けることをお奨めします。それがないと、休日かどうかの判断が出来ないでしょ？

補足

現在はＢ列に日付、Ｃ列に曜日、Ｄ列に有給か欠勤かの区別を入れていて、割増賃金の対象となる法定休日は日曜日で固定されているので、Ｃ列で休日かどうかの判断がつくようになっています。。