Jul 26, 2024 06:49 AM
Hello everyone, In Germany, there is a regulation in the Occupational Health and Safety Act that states workers who work on Sunday or on holidays receive an additional allowance on their salary. This involves a complex formula, which even ChatGPT struggles with. Maybe there is someone here who is smarter than AI.
My table includes a date field including time field named {Start} and a date field including time field named {End}. I want:
1. Formula field = formula that calculates the hours/minutes that fall within the period of Sunday.
Example : Max starts his shift at saturday 8 PM and works until Sunday 4 AM. The formula should now calculate 4 hours.
2. Formula field = formula that calculates the hours/minutes that fall within the period of holidays. The holiday dates should be defined in the forumla itselfs = ( 01.01.2024 / 29.03.2024 / 01.04.2024 / 01.05.2024 / 09.05.2024 / 20.05.2024 / 03.10.2024 / 25.12.2024 / 26.12.2024)
Example : Max starts his shift on 31.12.2023 at 8 PM and works until 01.01.2024 at 8 AM. The formula should now calculate 8 hours.
Can someone help me?
Jul 26, 2024 09:31 AM
Hello,
I have quickly tried something that seems to work but still needs heavy testing.
Also, be carefull, Holiday dates must never be on a sunday or extra time will be counted twice.
I also assumed that "start" and "end" do not cover more than 2 days.
Here is my table:
First point is that I had to set dates format as follows:
Field "sunday_extra_hours" is the formula:
Jul 27, 2024 05:38 AM
Hello Pascal,
Thank you very much for your quick response. Unfortunately, the formulas in their current form are not yet optimal for my needs, and I hope you can perhaps advise me on how to optimize them for my purposes.
To calculate the hours on Sundays, I only need one formula field that calculates the working time on Sunday in minutes, which I can then divide by the factor of 60 to obtain the working hours in hours.
I need the same for the hours on public holidays. Can these formula fields also be combined into one, so that, like the Sunday calculation, the working hours are shown in minutes and then also divided by the factor of 60?
I know this formula requires significantly more if-then conditions, but it would save me a lot of work for further calculations.
I look forward to your further help.
Jul 27, 2024 06:21 AM
Hello,
I do not think that trying to get the final result with one single formula makes it easy to control.
Here are 3 formulas that you can add to get the final result:
Extra_Minutes_Total gives you the total extra time in minutes: