How to Calculate Average Wages for Vacation in Excel

Accurate vacation pay calculations concern accountants, HR specialists (more specifically, dates and deadlines), and employees themselves. With Excel tools, some calculations can be automated. Let's explore creating simple programs for this purpose.

 

Calculating the Number of Vacation Days

We'll create a program to monitor the number of vacation days allocated to an employee. Public holidays are not included in vacation periods. So, let's start by creating an Excel table with public holidays.

To convert the text format to a numeric one, we'll use the DATEVALUE function.

DATEVALUE.

The formula combines values from cells B3 and C3 with the result from the YEAR function. To display holidays from the previous year, we use "-1". For the next year, "+1".

If the third argument isn't specified in the DATEVALUE function, it defaults to the current year:

=DATEVALUE($B3&$C3)

This formula yields: 01/01/2023.

On the next sheet, we'll create a table to calculate the number of vacation days allocated.

Employee data.

This is the first section of the table. In the second section, we'll display the total experience, days worked during the period, and the allocated vacation days. The header for this section is:

Header.

To automatically set the current date, we use the TODAY function.

For calculating total experience up to the current date, we use the formula:

=IF(C4="","Not date of receipt",DATEDIF(C4,D$3,"y")&" year "&DATEDIF(C4,D$3,"ym")&"months "&DATEDIF(C4,D$3,"md")&" days")

The number of days worked is the difference between the current date and the date of joining (=D$3-C4).

To find out the accumulated vacation days up to the current date, we use this formula:

Vacation days.

Extend these formulas down the column.

The third section determines the calculation period for the number of vacation days for the first year. The start of the period refers to the cell with the joining date. The end is calculated with the formula: =EDATE(G4;12)-1, where G4 is the start of the calculation period.

End.

The fourth section contains vacation data for the period. Enter the number of vacation days and the start date manually. Create an auxiliary column to calculate the end vacation date, considering holidays. The formula is:

=J4+I4-1+SUMPRODUCT(('public holidays'!$D$3:$G$10>=J4)*('public holidays'!$D$3:$G$10<J4+I4+SUMPRODUCT(('public holidays'!$D$3:$G$10>=J4)*('public holidays'!$D$3:$G$10<J4+I4+SUMPRODUCT(('public holidays'!$D$3:$G$10>=J4)*('public holidays'!$D$3:$G$10<J4+I4+SUMPRODUCT(('public holidays'!$D$3:$G$10>=J4)*('public holidays'!$D$3:$G$10<J4+I4+SUMPRODUCT(('public holidays'!$D$3:$G$10>=J4)*('public holidays'!$D$3:$G$10<J4+I4+SUMPRODUCT(('public holidays'!$D$3:$G$10>=J4)*('public holidays'!$D$3:$G$10<J4+I4+SUMPRODUCT(('public holidays'!$D$3:$G$10>=J4)*('public holidays'!$D$3:$G$10<J4+I4+SUMPRODUCT(('public holidays'!$D$3:$G$10>=J4)*('public holidays'!$D$3:$G$10<J4+I4))))))))))))))))

Excel adds the vacation days minus one to the start date. It then checks for public holidays within that range. If any exist, it adds their count to the total vacation days.

The formula for calculating the end vacation date is:

End date.

The total number of days taken off is calculated by: =IF(OR(I4="";J4="");0;I4+I5).

Using a similar algorithm, we prepare data for the second, third, and subsequent years of work. Towards the end of the table, you can have a summary column to calculate the remaining vacation days.

How to Calculate Annual Vacation Pay in Excel

The vacation pay calculation scheme is as follows:

  1. Calculate the annual salary for the year preceding the vacation. Only payments stipulated by the contract are included. One-time bonuses, sick pay, material aid, etc., are excluded.
  2. Compute the average monthly salary by dividing the annual salary by 12.
  3. Determine the average daily wage by dividing the average monthly salary by the average number of calendar days in a month (29.3).
  4. Multiply the average daily wage by the number of vacation days.

Data on the employee's salary for the period preceding the vacation:

The month, base salary, allowances (percentage), sick leave data, and material aid are entered manually (light blue cells). The number of additional payments can vary based on company specifics.

The salary data table has columns for calculations:

Salary sheet.

Formulas used to input salary data:

  • Number of working days (B3):
    • =NETWORKDAYS(A3; EOMONTH(A3; 0); 'public holidays 2023'!$D$3:$E$21)
  • Allowance (D3): =C3*$D$1
  • Actual worked time (E3): =IF(I3>0;B3-NETWORKDAYS(G3;H3);B3)
  • Bonus (F3): =IF(B3=E3;C3*20%;(C3/30.5*E3)*20%)
  • Days on sick leave (I3): =IF(OR(G3="";H3="");"";(H3-G3)+1)
  • Total accrued (L3):
    • =IF(J3<=0;C3+D3+F3+J3+K3;(C3/30.5*E3)+D3+F3+J3+K3)

On a separate sheet, create another table "Production Calendar" with holidays for each month:

Production calendar.

On another sheet, create a table for vacation pay calculations:

Vacation pay calculation.

Formula for cell C2: =salary!L3-salary!K3-salary!J3. Not all additional payments are included in vacation pay calculations. If they appear in the earnings table, they can later be deducted from the final figure.

Are vacation payments included in vacation pay calculations? No. Payment for a previous vacation is not factored into the new calculation.

By law, the calculation period is in calendar days. If a person worked a month according to the schedule (excluding weekends, business trips, etc.), that entire month is included in the period.

To calculate full months (C19):

=SUM(IF(salary!B3:B14=salary!E3:E14,1,0))

As well as the sum of days worked in incomplete months (C20):

=SUM(IF(salary!B3:B14<>salary!E3:E14,salary!E3:E14,0))

Note! Formulas in cells C19 and C20 are executed as an array; therefore, after inputting them, you should press the CTRL+SHIFT+Enter key combination.

Let's create an additional table:

Vacation Calculation.

Formula for calculating the number of days: =29.3*$H$14+29.3/30*$G$6.

Formulas for calculating average wages and vacation pay:

Similarly, vacation payouts are calculated upon termination in Excel, including compensation for unused vacation days.

To calculate maternity leave benefits in Excel, follow these steps:

  1. Determine the average earnings for the two years preceding the leave.
  2. Divide the obtained amount by the number of calendar days in the calculation period.
  3. Multiply the average daily wage by 30.4 and by 40%.

download file Download templates with example formulas for calculating average wages and vacation pay in Excel

The tables and formulas provided above are suitable for implementing the algorithm.


en ru