Calculating Sunday / Holiday Shift Allowances in the Staff Schedule

[MA_Production]

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?

[Pascal_Gallais-]

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:

IF(AND(WEEKDAY(start)=0,WEEKDAY(end)=0),HOUR(end)-HOUR(start)-1,

IF(WEEKDAY(start)=0,23-HOUR(start),

IF(WEEKDAY(end)=0,HOUR(end)

)))

Field "sunday_extra_minutes" is the formula:

IF(AND(WEEKDAY(start)=0,WEEKDAY(end)=0),60-MINUTE(start)+MINUTE(end),

IF(WEEKDAY(start)=0,60-MINUTE(start),

IF(WEEKDAY(end)=0,MINUTE(end)

)))

Field "start_holiday" returns 1 if start date is a holiday:

SWITCH(DATESTR(start),

"2024-01-01",1,

"2024-03-29",1,

"2024-04-01",1,

"2024-05-01",1,

"2024-05-09",1,

"2024-05-20",1,

"2024-10-03",1,

"2024-12-25",1,

"2024-12-26",1,

0)

Field end_holiday is the same formula using field "start" instead of "end"

Field "holidays_extra_hours" is a formula (difference with the sunday formula is that we can have to consecutive holiday days, 25th and 26th of december):

IF(AND(AND(start_holiday=1,end_holiday=1),WEEKDAY(start)!=WEEKDAY(end)),23-HOUR(start)+HOUR(end),

IF(AND(start_holiday=1,end_holiday=1),HOUR(end)-HOUR(start)-1,

IF(start_holiday=1,23-HOUR(start),

IF(end_holiday=1,HOUR(end)

))))

Field "holiday_extra_minutes" is the formula:

IF(AND(AND(start_holiday=1,end_holiday=1),WEEKDAY(start)!=WEEKDAY(end)),60-MINUTE(start)+MINUTE(end),

IF(AND(start_holiday=1,end_holiday=1),60-MINUTE(start)+MINUTE(end),

IF(start_holiday=1,60-MINUTE(start),

IF(end_holiday=1,MINUTE(end)

))))

Once again, I do not have tested any possible cases.

You still need to turn "extra hours" fields in minutes to add them up with the 2 "extra minutes" fields and put back the result in terms of hours and minutes.

Regards,

Pascal

[MA_Production]

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.

[Pascal_Gallais-]

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:

(sunday_extra_hours * 60) + sunday_extra_minutes + (holiday_extra_hours * 60) + holiday_extra_minutes

Extra_Hours gives the total number of hours from the total of minutes:

ROUNDDOWN((Extra_minutes_total / 60),0)

Extra_minutes completes extra_hours by giving your remaining minutes:

 MOD(Extra_minutes_total,60)

Of course, you could factorise these 3 fields into one, but I find it harder to follow and I do not see the advantage.

If you need to separate extra time due to sundays and extra time due to holidays, you would need to compute the total of minutes for each case and apply the "extra_hours" and "extra_minutes" formulas to each total.

Regards,

Pascal