Help

Calculating Night Shift Allowances in the Staff Schedule

Topic Labels: Formulas
Solved
Jump to Solution
155 2
cancel
Showing results for 
Search instead for 
Did you mean: 
MA_Production
5 - Automation Enthusiast
5 - Automation Enthusiast

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?

1 Solution

Accepted Solutions
patrick-F
5 - Automation Enthusiast
5 - Automation Enthusiast

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

See Solution in Thread

2 Replies 2
patrick-F
5 - Automation Enthusiast
5 - Automation Enthusiast

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
5 - Automation Enthusiast
5 - Automation Enthusiast

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

IT WORKS ! ! !