Vacation Time Accrual Spreadsheet– CALCULATE HOLIDAY DAYS IN EXCEL
The workers of a company are entitled to an annual holiday period, and depending on the labor laws of each country, a method is established to calculate the holiday days to which the worker is entitled.
In the vast majority of countries, the calculation of vacation days is based on the seniority of an employee within the company and therefore, the longer the seniority, the greater the number of vacation days to which they are entitled a person.
Vacation Time Accrual Spreadsheet
EXAMPLE OF CALCULATION OF VACATION DAYS
As an example, I will take the Federal Labor Law in Mexico that stipulates that workers with more than one year of service may enjoy a six-day holiday period which will increase by two days each year until it reaches twelve. After the fourth year of service, the increase will be two days of vacation for every five years. This rule can be summarized as follows:
1 year of service = 6 days of vacation.
2 years of service = 8 days of vacation.
3 years of service = 10 days of vacation.
4 years of service = 12 days of vacation.
From 5 to 9 years of service = 14 days of vacation.
From 10 to 14 years of service = 16 days of vacation.
From 15 to 19 years of service = 18 days of vacation.
From 20 to 24 years of service = 20 days of vacation.
From 25 to 29 years of service = 22 days of vacation.
Although our example of calculating vacation days will be based on Mexican laws, surely you can adapt the example to the particular case of your country, you should only pay special attention to the first step that is to translate the previous rule to an equivalence table.
EQUIVALENCE TABLE FOR VACATION DAYS
In order to calculate the vacation days in Excel it will be necessary to translate the rule established by the labor law making an equivalence between the years of service (seniority) and the corresponding vacation days.
The creation of this table is of the utmost importance because it will be our reference to obtain the vacation days that correspond to each worker according to their seniority. An indispensable requirement in the construction of this table is that the first column (Antiquity) must be ordered ascending or otherwise the formula we will use later will return erroneous results.
It is also important to mention that for the ranges of years indicated in the rule, it is sufficient to place the lower limit. For example, the rule says that from 5 to 9 years you are entitled to 14 days of vacation and in the table I have only placed the value 5 with your equivalent of 14 days of vacation. In the following steps you will see that this line will be sufficient to cover the range between 5 and 9 years of service.
CALCULATE THE ANTIQUITY OF AN EMPLOYEE
Now that we have created the equivalence table we can perform the following calculation that is the age of a person within the company. This calculation is obtained from the date of hiring so it is essential to have that data for each employee. In the following image you can see the sample information for three employees:
CALCULATE HOLIDAY DAYS IN EXCEL
The final step is to find the value of column F (Antiquity) within column A and get the corresponding value of column B that are the days of vacation. This is a job for the VLOOKUP function and we should only pay special attention to its fourth argument:
= VLOOKUP (F2, A2: B10, 2, TRUE)
When we use the VLOOKUP function it is very common to indicate the FALSE value in its fourth argument to indicate an exact search but now we need to perform an approximate search since there are ranges in the years of service. When we do an approximate search and the VLOOKUP function does not find the indicated value, it will return the immediate lower value. Observe the result of using the proposed formula:
How to calculate vacation days in Excel
For Hugo we get 12 days of vacation since it is the value that exactly matches the 4 year old row within the table. However, when searching for the value equivalent to Paco’s 12 years of service, the VLOOKUP function does not find an exact value, so it returns the immediate value below 12, which is the 10-year row which has the value of 16 associated. Holidays. The same happens when you get the vacation days for Luis, the VLOOKUP function returns the value corresponding to the row of 20 years that are precisely the 20 days of holidays that correspond to him.
This is how the approximate search of the VLOOKUP function helps us to easily implement a search within numerical ranges and in this way obtain the holidays in Excel for each of the employees in our example. If you want to know a little more about the fourth argument of the VLOOKUP function, consult the following article: The Ordered argument of the VLOOKUP function.
Finally, I want to say that the default value of the fourth argument of the VLOOKUP function is precisely TRUE, so we could omit it completely from our formula and continue to obtain the same vacation days. I have only insisted on the TRUE value to make a difference with the FALSE value that we generally use when making exact searches. In the following formula you can observe that the fourth argument is not present and nevertheless we continue obtaining the same previous results for the vacation days.
Gallery of Vacation Time Accrual Spreadsheet
( Click Image to Enlarge )
- Trucking Spreadsheet
- Tracking Spending Spreadsheet
- Task Manager Spreadsheet Template
- Steel Takeoff Spreadsheet
- Stamp Inventory Spreadsheet