Employee Vacation Accrual Spreadsheet – Calculate vacation days in ExcelCalculate vacation days in Excel
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 vacation 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 greater the number of vacation days to which he is entitled a person.
Vacation calculation example
As an example I will take the Federal Labor Law in Mexico which stipulates that workers with more than one year of service may enjoy a vacation period of six working days which will increase in two days each year to 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.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 calculation of vacation days will be based on Mexican laws, you can surely adapt the example to the particular case of your country, you should only pay special attention to the first step is to translate the previous rule to an equivalence table.
Holiday days equivalence table
In order to calculate vacation days in Excel it will be necessary to translate the rule established by the labor law by making an equivalence between the years of service (seniority) and the corresponding holiday days. This is how I came to the following table of data that reflects the detail of the rule described above:
Calculate vacation days in Excel
The creation of this table is extremely important because it will be our reference to obtain the holiday days that correspond to each worker according to their seniority. An essential requirement in the construction of this table is that the first column (Antiquity) must be sorted in ascending order or else the formula that 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 is entitled to 14 days of vacation and in the table I have only placed the value 5 with its equivalence 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 seniority of an employee
Now that we have created the table of equivalences we can make the following calculation that is the seniority of a person within the company. This calculation is obtained from the date of recruitment so it is essential to have this data for each employee. In the following image you can see the sample information for three employees:
Calculation of dates in Excel: Holiday days
To obtain Hugo’s seniority, I will use the SIFECHA function as follows:
= SIFECHA (E2, TODAY (), “y”)
The first argument of the function is the hiring date. The second argument is the date of today and the third argument will tell the function to get the difference between the two dates in years. By copying the formula down I get the seniority in years for each employee:
Holiday days calculation
The function SIFECHA returns the years completed and not a mere difference between the number of years and for that reason, Hugo will be 5 years old until August 10, 2014. If you want to know a little more about this function I recommend reading the following Article: The function SIFECHA in Excel.
Calculate vacation days in Excel
The final step is to find the value of column F (Antiquity) within column A and obtain the corresponding value of column B which are the days of vacation. This is a work for the function VARCHARV and we should only pay special attention to his fourth argument:
= SEARCHVAL (F2, A2: B10, 2, TRUE)
When we use the VARCHARV 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 FINDV function does not find the indicated value, it will return the next lower value. Note 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 row of 4 years of age within the table. However, when searching for the value equivalent to Paco’s 12 years of service, the VARCHARV function does not find an exact value so it returns the immediate value lower than 12 which is the row of 10 years which has associated the value of 16 Holidays. The same thing happens when you get the vacation days for Luis, the function SEARCHCARV returns the value corresponding to the row of 20 years that are precisely the 20 days of vacation that correspond to you.
This is how the approximate search of the VARCHARV function helps us to easily implement a search within numeric ranges and in this way obtain the days of vacations in Excel for each of the employees in our example. If you want to know a little more about the fourth argument of the function VARCHARV see the following article: The ordered argument of the VARCHARV function.
To conclude I want to say that the default value of the fourth argument of the function VARCHAR is precisely TRUE so we could omit it completely from our formula and continue to get the same vacation days. I have only insisted on the TRUE value to make a difference with the FALSE value we usually use when performing accurate searches. In the following formula you can observe that the fourth argument is not present and nevertheless we continue obtaining the same results previous to the days of vacations:
Employee Vacation Accrual Spreadsheet
Tag; Employee Vacation Accrual Spreadsheet,