Somebody joins or leaves a company during the year. Now how do you calculate the pro rata vacation days? Download my Excel tool to easily calculate pro rata holidays.
One of the difficulties when an employee joins or leaves a company during the year is often the calculation of pro rata vacation days. That’s usually when you can hear gnashing of teeth coming out of the HR Office.
But here’s a tool that will hopefully make your life easier.
What’s The Problem?
In Switzerland the law requires companies to calculate pro rata vacation days when an employee doesn’t work for a full 12 month period. However, it doesn’t explain how to do it.
Most experts recommend to calculate based on the vacation entitlement per month on a 30 day per month basis. But what about months with 28, 29 or 31 days?
Another method is to only count work days for partial months. This becomes however quite complicated if you want to create a formula instead of doing everything by hand.
My Method
Since I’m Swiss I opted for a compromise: use 1/12 of the annual vacation entitlement for full months and the exact ratio of calendar days for partial months.
Example
Somebody joins the company on 16 November with 24 vacation days for a full year.
December is the only full month, so the result is 2 days (24 days / 12).
The number of contractual days in November are 15 ([30 days for the month of November] – 16 + 1; “+1” since the 16th is included). So the entitlement is (15/30)* 2 = 1 day
Total: 2 + 1 = 3 days
The Excel File
If you’re already bored you can just download the file here. It was created in Excel 2003, so it should work even if you’re running an older version of Excel.
More Details
You need to enter the year since the tool takes into account whether February has 28 or 29 days.
If somebody works part-time change the workshare. However, it’s easier to consider the days worked instead of the hours. For instance, somebody might work 5 days per week but only 4 hours per day. In this case I recommend to use “100%”.
And just in case, the tool works for a 5-day workweek at 100%. If you have more than 5 days per week then this tool might not give you the results you need.
Copyright and Disclaimer
Of course, you’re using this tool at your own risk and peril. I spent some time on the testing and tried to imagine as many possible screw ups as possible. But it’s still your own responsibility, so lets say that this tool is for educational purposes only.
You’re welcome to distribute, use and modify the tool. But you’re not allowed to sell it.
Feedback
Please let me know if you find this tool useful or if there’s something you don’t like about it. And in case you should find a bug, then I really want to know about it 😉
Again, here’s the link to download the Excel file.
