勤務表のエクセル作成で数式を教えてください。

エクセル

勤務表の作成で数式を教えてください。

タイムカード打刻時間 計算時間
出社時間 退社時間 出社時間 退社時間
8:41 19:42 9:00 19:20
D7 E7 F7 G7

上記のように、D7、E7がタイムカード打刻時間で、
集計ができるように計算時間をF7、G7に変換していきたいです。

定時時間が、9：00～17：45です。
残業は、17:50～30分単位
18：20で30分残業
18：50で1時間残業
19：20で1時間30分残業
19：50で2時間時間
20：20
20：50
という感じです。

F7の方は上手な数式になっているかわかりませんが、何とか9:00になるようにできました。

G7の方は、タイムカード打刻時間の退社時間E7が17:45～18:17の間だったら、17:45で返す。
18:20以降だったら、17:50から30分単位で返す。
18：20
18：50
19：20
19：50
20：20
20：50
この間隔で返していきたい。

しかも、残業時間は3分くらいは足りてなくても、残業に入れちゃうそうです。
打刻が18:17くらいでも、18:20にして、30分の残業になります。

定時の時間等、参照の時間は、別表にしてみたりしてみましたが、
定時が17:45で残業の開始時間が17:50のため、30分間隔の式を入れても、ずれてしまいます。

=IF(($E7>=O$8)*($E7<=P$8),$P$6,IF(D7="","",-INT(-E7*48)/48-"00:30"))

O8に9:00　P8に17:45　と定時時間を別表にしました。

でも、17:50～30分単位にしないとずれます。

説明が下手で伝わらないでしょうか。。。

もしわかれば、よろしくお願いいたします。

No.1 回答者： rexfan 回答日時： 2023/01/17 08:24

目的は残業時間の計算なのに、17:45にこだわってはいけません。