Calculating Night 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 between 11 PM and 6 AM 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 a formula that calculates the hours/minutes that fall within the period from 11 PM to 6 AM.

Example: Max starts his shift at 8 PM and works until 4 AM. The formula should now calculate 5 hours.

Can someone help me?

[patrick-F · Accepted solution]

Hi MA_Production,

Below is a proposal for your formula. It should output the number of hours worked within the higher rate period.
Please note that for the formula to work properly, you would need to set the field for the "start time" and the "end time" to "use the same time zone for all collaborators".

If you need to change the times, please edit the fields in red (11pm and 7 as in 7 hours later)

Formula:

MIN(-DATETIME_DIFF(REPLACE(DATETIME_FORMAT({Start time}),12,8,"23:00:00"),{End Time},"minute")/60,7

)

-

MAX(-DATETIME_DIFF(REPLACE(DATETIME_FORMAT({Start time}),12,8,"23:00:00"),{Start time},"minute")/60,0)

Please let me know if you have any questions! 
Website: alessiomonino.com
Calendly: https://alessiomonino.com/contact
Email: patrick@moninosolutions.com

[MA_Production]

Hey patrick-F,
many many thanks for your support !

IT WORKS ! ! !