Table of Contents
To calculate the average employee tenure in Excel, you will need to gather the data for each employee’s start date and current date of employment. Then, create a new column and use the formula “=Today()-Start Date” to calculate the number of days each employee has been employed. Next, use the formula “=AVERAGE(range of days)” to find the average number of days worked. Finally, convert the average number of days into years by dividing by 365 and rounding to the nearest whole number. This will give you the average employee tenure in years.
Calculate Average Employee Tenure in Excel
Often you may want to calculate average employee tenure at some company in Excel.
Fortunately this is easy to do and the following step-by-step example shows how to do so.
Step 1: Enter the Data
First, let’s enter the following dataset that shows the start and end date for various employees at some company:
Step 2: Calculate Tenure of Each Employee
Next, we can type the following formula into cell D2 to calculate the tenure of the first employee:
=YEARFRAC(B2, C2)
We can then click and drag this formula down to each remaining cell in column D:
Column D now shows the tenure (in decimal years) of each individual employee.
Note: The YEARFRAC function returns the number of decimal years between a specific start and end date.
Step 3: Calculate Average Tenure
Lastly, we can type the following formula into cell D11 to calculate the average employee tenure:
=AVERAGE(D2:D11)
The following screenshot shows how to use this formula in practice:
If you would like to display this number in terms of years and months, you can type the following formula into cell E11:
=INT(D11) & " years, " & INT((D11-INT(D11))*12) & " months"
The following screenshot shows how to use this formula in practice:
The average tenure of employees at this company is about 4 years and 3 months.
Additional Resources
The following tutorials explain how to perform other common tasks in Excel: