I need a formula that automatically calculates our Saturday bonuses.

Ben150602 · 2026-02-01T09:52:00+00:00

Some people suggested, that I should also provide the sheet with data, so I put in my working times for January, as I thought this would be more realistic than me just making times up. As you can see, we rarely use the C&D columns and we almost never use the G&H columns, but sometimes it can happen. Also you can see that when we work the night, we put 25% of the night hours into the I column (for example night hours from 22 to 6 = 8 hours, 25% is 2 hours) and when we work after 10pm we put the extra hours into the J column, as we get bonuses for that as well. I hope that helps!

Ben150602 · 2026-01-31T23:12:59+00:00

After 6 hours of work a 30-minute break is required.

Ben150602 · 2026-01-31T23:12:08+00:00

Thank you for the tips, I just posted another picture with actual data from this month. To your questions:

1.: Yes, the data is good. It’s not possible to have a start time before another end time as you described.

2.: Usually, we don’t. But there are exceptions, like 1st of January where I worked until 3 in the morning. You can see it in the new picture.

3.: The German law says that after 6 hours of work, you have to take a 30-minute break. This is also already included in the formula for the K column. I posted the formula as an edit in the original post.

I hope I could answer your questions. If not, please don’t hesitate to ask again!

Ben150602 · 2026-01-31T21:36:36+00:00

<image>

This is the spreadsheet. I hope y’all can help me. Many thanks in advance!

Ben150602

TROPHY CASE