エクセル関数で勤務表の計算をしなければなりません。 休憩時間を自由にとるしくみなので残業の計算式の作り方がわかりません…

Question

エクセル関数で勤務表の計算をしなければなりません。
休憩時間を自由にとるしくみなので残業の計算式の作り方がわかりません
どなたかよろしくお願いいたします。条件は
出勤時間はバラバラのため定時出勤時間がありません。

1日は9時間拘束、8時間を実働がこえると1時間以上好きなだけ休憩をとることができます
要するに無給ということです
残業割増は8時間以上の実働が該当します。
深夜は22時～翌5時までが深夜割増時間帯です。
日をまたぐ時間に労働をしていますので休憩を深夜時間帯にとるのか通常時間帯にとるのかで同じ時間勤務していても計算の単価が違うので素人の私には難しいのです。

例①出勤22:00 退社 8:00(32:00) 休憩29:00～30:00 の場合 残業1時間 深夜割増7時間
例②出勤22:00 退社 8:00(32:00) 休憩28:00～29:00 の場合 残業1時間 深夜割増6時間
例③出勤22:00 退社 8:00(32:00) 休憩28:30～29:00 、30:00～30:30 の場合 残業1時間 深夜割増6時間30分
などとなります

また、実際に休憩は何回(下の表では④回分)にも分けてとっているので元の時間データをどのように利用するのが便利なのかも悩んでいます。よいアドバイスをお願いします。

A B C D E F G H I J K L M
1 出勤 退社 休憩時間帯① 休憩時間帯② 休憩時間帯③ 休憩時間帯④ 深夜割増 残業
2 22:00 32:00 29:00 30:00 ? ?
3 22:00 32:00 28:00 29:00 ? ?
4 22:00 32:00 28:30 29:00 30:00 30:30 ? ?
?の部分に入る関数についてよろしくお願いします。

Answer 1

ちょっと言葉でいうのは難しいのですが。。。

下の画像でいうならば、
①ＣとＦには時間、ＥとＨには分、Ｉには休憩時間の合計時間を入れます。
②Ｌの「合計」の欄には「=IF(C5=0,"",F5+H5/60-C5-E5/60-I5)」と関数を入れます。
これでその日働いた総時間が分かります。
③Ｊの「基準内」には「=IF(C5=0,"",IF(L5<8,L5,8))」が入っています。
④Ｋの「基準外」には「=IF(C5=0,"",IF(L5-8<0.1,"",L5-J5))」が入っています。

これで残業時間は分かりますが、
深夜の時間は休憩によって変わるのでそれを式にして入れるとなると
もっと複雑なことになるので、私は自分で計算しています。

なお、うちの会社は定時が７時間１５分なので
時間と分を分けて入れないと、何かと問題が出てきてしまい、
現在のこの形が一番いいのかなということになりました。

ｅｘｃｅｌでは限界があるので、もっと別のソフトを使ったほうが良いかもしれません。

うちの会社では、タイムカードで出勤と退勤と深夜時間は計算できますが、
時間休などの場合はやはり手計算になります。

私もあまりｅｘｃｅｌは得意な方ではないので、
参考程度にしてもらえると良いですが。