Tips and Tricks: How to handle complex holiday rules for cross midnight shifts in time tracking
Have you ever come across a situation or requirement where you pay different premiums for different holidays? For example, say for Christmas holiday you get 150% premium but for May 1st Labor Day you only get 125% premium. To add to this, you might have a situation where a normal holiday (where 125% is paid) is followed by a High Holiday next day (where 150% is paid) and vice versa. Further, a normal day can be followed by a full holiday. These situations become tricky especially for cross midnight workers where part of the day could be a normal holiday and other part across the night could end up with a high holiday/normal day.
Do you want to understand and handle these tricky cases and situations? If yes, please read through my blog below where I have explained how you can deal with these complex scenarios in time tracking. The blog is going to be huge as we have lot of time valuations to cover, and it is necessary to explain all the steps in detail to make you understand the configurations which goes beneath that.
These cases typically apply to European countries like Germany. I am going to demonstrate this via a sample requirement. This may not be the exact requirement you might have but nevertheless, the methods covered to handle this should not vary much.
So, let’s get started
First thing first, if you are looking to handle the normal weekday, and Sunday cases for cross midnight shift, you can look at my previous blog on Germany
Cross Midnight Recording Germany
So, this blog would be a continuation from the previous blog especially for Holidays
Sample Requirements for Holiday
- Night Shift premiums are paid from 12:00 AM to 04:00 AM next day (covers 28 hours window)
- Normal Holidays get 125%
- High Holidays get 150%
Example 1: An employee starts his shift on April 30th at 10 PM and ends his day on 5 AM in the morning next day.
From 10 PM– 12 AM he gets normal day (say 25% tax free ) premiums paid out
From 12 AM – 05 AM he gets holiday premium (125% tax free) paid out as part of his shift is a holiday next day
Example 2: An employee starts his shift on Dec 24th at 10 PM and ends his day on 5 AM in the morning next day (Dec 25th).
From 10 PM– 12 AM he gets normal holiday premium (125% tax free) paid out
From 12 AM – 05 AM he gets high holiday premium (150% tax free) paid out as part of his shift is a high holiday next day
Note: These are tax free percentages and are decided by local regulations/company regulations. Hence please treat these percentages as just examples and not actual laws.
Sample Holiday Configurations
First create the necessary holiday categories for High Holidays and use them in your holiday calendar. For Normal Holidays you can use the default category “Full”
The above holidays are just examples and may not necessarily have correct descriptions. The important thing here to note is dates and categories (Full, Higher Holiday Premium)
Sample Time Valuations and explanations
The structure of the time valuations would be to first handle and filter all holiday cases , then deduct holiday times from normal net working times and then finally the normal net-working times would be used for other normal days which do not have any holiday part
The first three time valuations are self-explanatory to get net working time without dynamic breaks. Hence, I will not explain this in details (It is also covered in our best practices for time tracking Germany if you have not seen that before)
Time Valuation: Public Holiday 125% premium 125% tax free 00:00 to 00:00
The first-time valuation uses the normal holiday class filter and gets the time from 12 AM to 12 AM. The result is moved to time type group below DE_HOL1. Very Simple
Time Valuation: Public Holiday 125% Premium 00:00 to 04:00 next day
Similar to the previous time valuation but it fetches the time segment from 00:00 to 04:00 AM Next Day. Please ensure to mark the next day indicator in time segment filter so that times of next day which is cross midnight is captured as of the current day shift. The result is moved to time type group below DE_HOL2
Time Valuation: Check Next Day High Holiday
This time valuation checks if the next day is a high holiday. Please take a note of the time records filter. You should use “Holiday Category Filter for Next day” in the time records filter . The day indicator in the corresponding time segment also should be marked as “Next Day”. The result time segment from 00:00 to 00:00 is then moved to time type group below ZNK_HHND1
Time Valuation: Compare if next high day detected and negate the core night (00:00 -04:00)
The time valuation uses a comparison method to check if the next day is a high holiday or not. Hence comparison group used is ZNK_HHND1 (High Holiday) from previous time valuation result. If this is greater than threshold of 0, it indicates that next day following current holiday day was indeed a high holiday. If it is 0, then next day is not high holiday.
If>0, the input group DE_HOL2 (segment from 00:00 to 04:00) is moved to Time Type Group above which is DE_HOL2B (High Holiday Detected)
If<=0, the input is moved to time type group below DE_HOL2C (next day not a high holiday)
Time Valuation: Public Holiday 125% Core Night Check 00:00 to 04:00 (DE_HOL1B)
In this time valuation, the real core night check for 00:00 to 04:00 next day is performed. It is again a comparison method where the comparison group now is DE_HOL1( Normal Holiday 125% segment from 00:00 to 00:00) is checked with threshold 0.
If>0 indicates that there was some time recorded before midnight on a normal holiday. In that case, the normal holiday 125 % tax free premiums are paid as well from 00:00 to 04:00 next day. Hence move the input DE_HOL2C (result where next day is not a high holiday) to time type group above DE_HOL1B
If the input is Null or O, this time valuation is skipped all together as input is Zero and there will be no records in time type group above. This indicates that next day was indeed a high holiday and if you refer back to the previous evaluation, DE_HOL2B (High Holiday Detected) would have a result accordingly.
So, these two time valuations would work in conjunction and take care of handling the case of next day (whether high holiday has to be paid or not)
So far so good. Phew ..These are complex to explain in simple words but I hope I did a pretty good job to make you understand the flow so far
Other Time valuations for other holiday categories
The next set of time valuations as listed below are similar to Normal Holiday 125% ones but time records filter and holiday categories are different as per the holiday premiums
It is impractical to go through all these individually, but I hope you got some idea on how to deal with these types already
I will just place screenshots of these without any further explanations which would be self-explanatory
Now back to another important time valuation. This is optional if you want to deduct holiday times from normal working times (on other working days). If you want to combine the tax free percentages of holiday and also for that day in particular, you can skip this step. For example, if Monday May 1st 2023 is a public holiday and you want to pay 125% (Holiday part) and 25% for Monday part , then you don’t have to deduct holiday times from Normal working times.
Time Valuation: Deduct Holiday Times from net working time
You can see that all holiday times have reverse sign as “Yes” and the Net working time which we spoke about at the beginning of this blog is set to “No”. So the end result would be net working time without matching holiday times. This Net time “Revised Non Holiday Time Final” (DE_WORK_NET_WOPH_FINAL) is then used further as input for other time valuations which deal with Mon- Fri and Sundays
So, this part concludes how to handled Holiday Cases and situation where a normal holiday is followed by a high holiday.
Now coming to another case where a normal day is followed by a Public Holiday paid at 125%
We need the following sequence in this case
The first 3 time valuations to get time segments for respective Mon-Fri Days from 20: 00 to 00:00, 00:00-04:00 and 04:00 – 06:00 is already explained in my previous blog. Hence I would not be repeating it here
First, we need to check if the day following the current normal day is a holiday or not
Time Valuation: DE Check Next Day Public Holiday (ZNK_PHND1)
In this case we need to use the holiday filter for next day. This is because the employee starts the day on a normal day say at 10 PM and goes all over until next day 05 AM where the part after midnight is a holiday now
If the rule is satisfied, the time segment is then moved to the time type group below ZNK_PHND1 (Next Day PH)
Time Valuation: Normal Current Day with next day as Holiday for 00:00 – 04:00 (DE_NormalDay_NextDayHol1)
Next we now check the result of previous evaluation ZNK_PHND1 (Next Day PH) as a comparison group
If >0, this indicates that next day was a holiday indeed. Hence move the result of core night Mon-Fri from 00:00 – 04:00 to Time Type Group Above DE_CN2A (Next day Holiday Detected)
If <=0 , this indicates that next day was not a holiday. Hence move the result of core night Mon-Fri to Time type group below DE_CN2B (No Holiday Next Day Mon – Fri)
Time Valuation: Normal Current Day with next day as Holiday for 4:00 -6:00 (DE_NormalDay_NextDayHol2)
If next day is a PH, the whole day is eligible for 125% tax free premium. Hence we also need to check for core night Mon-Fri 4:00 to 06:00 segment. It is similar to previous time valuation where we check if next day was a PH with help of comparison group
If>0 , then segment 04:00 – 06:00 is moved to DE_CN3A ( Mon – Fri next day holiday detected)
If <=0, then segment 04:00 – 06:00 is moved to DE_CN3B ( Mon – Fri next day No holiday detected) where normal day premiums up to 25% tax free
Time Valuation: Core Night Check Mo – Fri (00:00 – 04:00) (DE_CN4V2)
This is the actual core night check for Mon- Fri 00:00 – 04:00 where we decide whether to pay 25% tax free or up to 40% tax free premiums. If the employee works before midnight (Comparison group DE_N1_25V2 )and provided next day is not a holiday, the input group DE_CN2B is moved to time type group above DE_CN4V2 which pays premium up to 40% tax free limit
Else premium is paid up to 25% tax free if there are no recorded times before midnight
This concludes the config Part
Cases not covered in this blog:
- A normal holiday followed by another normal holiday next day
- A high holiday followed by a normal holiday next day
- Normal Day followed by a High Holiday next day
But by now you should have got an idea on how to handle these cases like how we have done it in this blog. I leave that as an exercise for you all.
It’s not done until we test this out. So, lets test few cases now
Night Shift starts on a Holiday and ends on a High-Holiday (with Recording before midnight)
In this example, May 2nd is a normal holiday and May 3rd is a high holiday
The employee works from 10 PM on May 2nd till 06:00 AM on May 3rd
Hence 2 hours from 22:00 to 00:00 is paid at 125% (Normal Holiday)
The remaining part of 5 hours 30 mins post-midnight is paid at 150% (High Holiday)
Night Shift starts on a Holiday and ends on a High-Holiday (with No Recording before midnight)
Similar to last one but since employee has started working only post-midnight, the entire 6 hours is paid at 150% applicable for High Holiday
Night Shift starts on a Normal Day and ends on a Holiday (with Recording before midnight)
Monday May 8th is a normal day followed by a holiday on May 9th. Employee starts his shift from 22:00 PM on May 8th until 05:00 AM on May 9th
2 hours from 22:00 to 00:00 are paid at normal day rates 25% tax free
The part after midnight 4 hours 30 Min are paid at 125% taxfree
Night Shift starts on a Normal Day and ends on a Normal Day
This is self-explanatory
Normal day 25% / 40% tax free premiums are paid out as per normal day rules.
As you can see, we have handled complex cases of holiday in conjunction with normal day .
And again, I want to emphasize that these are mere examples and may not reflect the exact laws in your region. But All I wanted to communicate is how we can deal with such cases using filters and comparison time valuations
Hope this helps
All the best
That's actually useful, thanks! I can say that I have a team of employees, and my company uses the same premiums for holidays and night shifts. I already use a time tracker for Mac to increase the productivity of my employees, get detailed reports about their activity, and calculate payroll, and the information from your article can help me do that even more precisely.
Thanks for the feedback