Calculating Average Employee Tenure using formula in Excel - HR KPIs & HR Metrics

Опубликовано: 10 Февраль 2025
на канале: Indzara - Simple and Effective Templates

18,226

149

Step-by-step tutorial on how to calculate average employee tenure of a company using formulas in Excel.

00:00 Introduction
00:14 Overview of data
00:34 What is Employee Tenure? Definition of Employee tenure
01:14 Calculate and store Today's date
02:46 Reminder: Store the input data as table. See • Calculating Number of Employees easil...
03:20 Calculate Tenure (in Months) for each employee using DATEDIF function
05:27 Calculate Tenure in Years for each employee using ROUND function
07:19 Calculate average tenure for all employees using AVERAGEIF function
09:12 Displaying average tenure in years and months using INT function

Tutorial: https://indzara.com/2021/09/hr-kpi-av...

Visit https://indzara.com/hr for Excel templates and tutorials related to HR Analytics, HR metrics, HR KPIs and HR Dashboards.

Functions used: DATEDIF, INT, ROUND, AVERAGEIF, TODAY
how to calculate HR metrics
#hranalytics #hranalysis #hrmetrics #hrkpis #excel #peopleanalytics

I would love to hear from you how you calculate this HR metric in your company. Please let me know in the comments.

Excel Tutorial
Let’s first calculate tenure for each employee.
Tenure is the time from Employee’s Start date to Today. Average Employee Tenure is the average of all active employees in a company.
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.=TODAY()

We can now write a formula for a new column ‘TENURE (YRS)’ in the table.
Formula =ROUND(DATEDIF([@[START DATE]],TODAY,"m")/12,1)

Now, let’s calculate the average tenure for all active employees.
Formula =ROUND(AVERAGEIF(T_EMP[STATUS],"Active",T_EMP[TENURE (YRS)]),1)

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.

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