In this article, we will calculate a common HR KPI – Average Employee Tenure, using simple formulas in Excel.
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
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.
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.
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.
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.
Now, let’s calculate the average tenure for all active employees.
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.
As a bonus, to make the display show number of months, we could add another formula
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
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.