How to Calculate Average Tenure of Employees in Excel: 2 Suitable Ways

Method 1 – Combine DATEDIF, TODAY, and AVERAGE Functions to Calculate Average Tenure of Employees

1.1 Dynamic Date

Steps:

  • Select cell D5, and write down the below formula.
=DATEDIF(C5,TODAY(),"M")

Formula Breakdown:

  • Inside the DATEDIF function, C5 is the start_date, TODAY is the end_date, and M is the format of the DATEDIF function.
  • Since we want to see the result on the current day, it’s ideal to calculate using the TODAY function. If you use the TODAY function, when you open this workbook for a couple of days/months (whenever you like), it will show the result based on that day.
  • We put M to get the difference in months.
  • Press Enter on your keyboard. Get the tenure which is the return of the DATEDIF function. The return is 21.

Combine DATEDIF, TODAY, and AVERAGE Functions to Calculate Average Tenure of Employees

  • Autofill the functions to the rest of the cells in column D.

Combine DATEDIF, TODAY, and AVERAGE Functions to Calculate Average Tenure of Employees

  • Calculate the average tenure now. To calculate the average tenure, write the AVERAGE function in cell E5.
=AVERAGE(D5:D9)
  • Press Enter on your keyboard. Get the tenure which is the return of the AVERAGE function. The return is 2.
  • Within the AVERAGE function insert the range of your calculated tenure for each employee, it will give you average tenure.
  • We showed the difference in months. Get the output in years as well. Just use Y instead of M.

Combine DATEDIF, TODAY, and AVERAGE Functions to Calculate Average Tenure of Employees

Note:
In the above sub-method, you can also use the NOW function instead of the TODAY function. The result will remain the same.

1.2 Specific Date

Steps:

  • Select cell E5, and write down the below.
=DATEDIF(C5,D5,"M")
  • Press Enter on your keyboard.Get the tenure which is the return of the DATEDIF function. The return is 24.

  • AutoFill the functions to the rest of the cells in column E.

  • Calculate the average tenure, write the AVERAGE function in cell E5.
=AVERAGE(E5:E9)
  • Press Enter on your keyboard. Get the tenure which is the return of the AVERAGE function. The return is 2.
  • Here we got the result in monthly format.

Applying DATEDIF Function to Calculate Tenure in Year-Months Format


Method 2 – Applying DATEDIF Function to Calculate Tenure in Year-Months Format

Steps:

  • Write the DATEDIF formula in cell G5.
=DATEDIF(C5,D5,"Y")&"Y "&DATEDIF(C5,D5,"YM")&"M "
  • Press Enter on your keyboard. Get the tenure in year and month format which is the return of the DATEDIF function. The return is 2Y 0M.

Applying DATEDIF Function to Calculate Tenure in Year-Months Format

  • AutoFill the functions to the rest of the cells in column D.
  • We inserted Joining Date and Leaving Date in the placeholders for start_date and end_date and concatenated Y and M to the outside of the DATEDIF function so that the result would be presented with a unit.

Applying DATEDIF Function to Calculate Tenure in Year-Months Format


Employees Tenure Calculator

You can use today’s workbook as a calculator to count average employees’ tenure. There is a sheet named Calculator.

Explore that sheet. You will find fields for the Joining Date, and Leaving Date. Insert your values. It will calculate Tenure and the Average Tenure which has been given in the below screenshot.

Employees Tenure Calculator

For your understanding purpose, we gave an example with the values of three employees. You can insert as many employees as you want.


Download Practice Workbook

You can download the practice workbook from the link below.


Further Readings


<< Go Back to Days Between Dates | Calculate Dates | Date-Time in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Shakil Ahmed
Shakil Ahmed

My name’s Shakil. My articles are targeted to support you in enriching knowledge regarding different features related to Microsoft Excel. I am fond of literature, hope some knowledge from them will help me providing you some engaging articles even though some weary technical terms.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo