Calculating Average Employee Tenure in Excel

In this article, we will calculate a common HR KPI – Average Employee Tenure, using simple formulas in Excel.

Data

Let’s assume the dataset has a list of employees with a Status column to indicate Active vs Inactive employees. The table is converted to an Excel Table with name T_EMP

Input Data - Employee List — Input Data – Employee List

Definition

Tenure is the time from Employee’s Start date to Today. Average Employee Tenure is the average of all active employees in a company.

A longer average employee tenure indicates that employees are staying with the company for a long period of time which in turn means employees are happy and satisfied with the company.

Video Demo

Calculate tenure for each employee

Let’s first calculate tenure for each employee.

Since we want to do this calculation for each employee, we should store the today’s date value once and re-use, instead of calculating TODAY for each employee.

Formula for calculating today’s date.

Formula

=TODAY()

I am calling that calculated cell as TODAY (you can name it as you wish).

We can now write a formula for a new column ‘TENURE (YRS)’ in the table.

Formula

=ROUND(DATEDIF([@[START DATE]],TODAY,”m”)/12,1)

There are three operations we do in this formula

We use a function DATEDIF (which does not actually appear in newer versions of Excel – however if you type the function name and parameters, it works) to calculate the number of months from Start date to TODAY. The parameter “m” tells Excel to calculate the number of months.
I am first calculating the number of months and then divide by 12 to arrive at number of years including fractional year.
I then round to 1 digit using ROUND function to show tenure like “7.6 years”.

Now the table should look like below.

Added tenure calculation for each employee

Now, let’s calculate the average tenure for all active employees.

Formula

=ROUND(AVERAGEIF(T_EMP[STATUS],”Active”,T_EMP[TENURE (YRS)]),1)

There are two parts to this formula.

We use the AVERAGEIF function to only average the Tenure (Yrs) column if the Status is Active.
Then we round to 1 digit decimal place using ROUND function.

Bonus

As a bonus, to make the display show number of months, we could add another formula

Formula

=INT(G3)&” Years “&ROUND((G3-INT(G3))*12,0)&” Months”

G3 here represents the Average tenure in Years we calculated earlier.

In the above example, we can see that the second display shows 8 Years 8 Months.

Functions used: DATEDIF, INT, ROUND, AVERAGEIF, TODAY

Conclusion

I hope this was useful. In your company, how do you calculate the average employee tenure? I would love to hear from you in the comments section.

Read More

Leave a Reply Cancel reply