How to Calculate Average Tenure of Employees in Excel

Excel is a useful tool for you when calculating the average tenure. Today, I’m going to demonstrate four easy and effective ways of how to calculate the average tenure of employees in Excel, with examples. I’m making examples and the workbook with Microsoft 365.


📁  Download Excel File

To prepare this article, we used an Excel workbook, which is available for download. Additionally, you can edit and customize the data you enter while viewing the results.


What Is Tenure?

When talking about an employee’s job, you will hear the word “tenure” more often. Tenure is how long an employee has worked for a certain company. In plain English, this is like an employee’s length of service.


Learn to Calculate the Average Tenure of Employees in Excel with These 4 Examples

Let’s get started with our dataset. Our data set has several employee tenures from the company called SOFTEKO. To get the average tenure, we will use the DATEDIF , TODAY , YEARFRAC and AVERAGE functions. Details of each function are provided in each example.


Example 1

Estimating Average Tenure Years of Employees

Suppose that you are measuring the tenure of your former employees and that each one of them has a time scale. Then we will use DATEDIF function. It determines how many days, months, or years there are between two dates. Start date, end date, and format are the three parameters required by the DATEDIF function. Finally, we will use the AVERAGE function. The central tendency of a bunch of numbers in a statistical distribution is measured by the AVERAGE function.

⬇️⬇️ STEPS ⬇️⬇️

  • Select cell E6 and enter the following DATEDIF function first.
=DATEDIF(C6,D6,”Y”)
  • Then simply press the Enter key on your keyboard. Thus, you will receive the DATEDIF function’s return. The result is 3.

datedif formula to calculate average tenure of employees in years in excel

  • Autofill the remaining column E cells as well.

autofilling datedif's year formula to calculate average tenure of employees in excel

  • Once that is done, figure out the average tenure. In cell F6, enter the AVERAGE function to determine the average tenure.
=AVERAGE(E6:E11)
  • Now press the Enter key on your keyboard. The tenure, which is the return of the AVERAGE function, is 3.

calculate average tenure of employees in excel with average after datedif

🔨  Formula Breakdown

👉 C6 is the start date and “Y” is the DATEDIF format inside the DATEDIF funtion.

👉 Basically, we used “Y” to get the difference in years.

📕 Read More: 8 Ways to Calculate Age Using Formula on a Specific Date in Excel


Example 2

Computing Tenure of Current Employees

In this sub-method, you might need to see the tenure (service duration) of your current employees. You can calculate the outcome by the difference between the current date and their joining date. We will use the DATEDIF function. It determines how many days, months, or years there are between two dates. Start date, end date, and format are the three parameters required by the DATEDIF function. Then we will use the TODAY function. It returns the current date’s serial number. Excel calculates the date and time using the serial number as the date-time code. Finally, we will use the AVERAGE function. The central tendency of a bunch of numbers in a statistical distribution is measured by the AVERAGE function.

⬇️⬇️ STEPS ⬇️⬇️

  • First, select cell D6 and write the following functions.
=DATEDIF(C6,TODAY(),”M”)
  • Now press the Enter key on your keyboard. As a result, you will receive the tenure, which is the DATEDIF function’s return. Here 3 is the return.

  • Autofill the remaining column D cells as well.

datedif month formula to calculate average tenure of employees in excel

  • Now figure out the average tenure. In cell E6, enter the AVERAGE function to determine the average tenure.
=AVERAGE(D6:D11)
  • Now press the Enter key on your keyboard. The tenure, which is the return of the AVERAGE function, is 5.8.

calculate average tenure of employees in excel from datedif month formula

🔨  Formula Breakdown

👉  C6 is the start date and TODAY() is the end_date and M is the DATEDIF format inside the DATEDIF function.

👉 It’s better to calculate using the TODAY function because we want to see the result on the current day. If you use the TODAY function, the result will be displayed based on that day anytime you open this worksheet even after a few days/months.

👉  Basically, we used “M” to get the difference in months.

📕 Read More: 4 Suitable Ways to Add Sequential Dates Across Sheets in Excel


Example 3

Calculating Average Tenure of Employees in Months

Suppose that you are measuring the tenure of your former employees and that each one of them has a time scale. Then we will use the DATEDIF function. It determines how many days, months, or years there are between two dates. Start date, end date, and format are the three parameters required by the DATEDIF() function. Finally, we will use the AVERAGE function. The AVERAGE function measures the central tendency of a bunch of numbers in a statistical distribution.

⬇️⬇️ STEPS ⬇️⬇️

  • Select cell E6 and enter the following DATEDIF function first.
=DATEDIF(C6,D6,”M”)
  • Then simply press the Enter key on your keyboard. As a result, you will receive the tenure, which is the DATEDIF function’s return. The result is 40.

Calculating Average Tenure of each Employees in Months

  • Autofill the remaining column E cells as well.

Calculating Average Tenure of all Employees in Months after autofill

  • Now figure out the average tenure. In cell F6, enter the AVERAGE function to determine the average tenure.
=AVERAGE(E6:E11)
  • Now press the Enter key on your keyboard. The tenure, which is the return of the AVERAGE function, is 46.8.

🔨  Formula Breakdown

👉 C6 is the start date and M is the DATEDIF format inside the DATEDIF function.

👉 Basically, we used “M” to get the difference in months.

Example 4

Counting Number of Year Fractions of Employees

If all you want to know tenure of employees, the YEARFRAC  function is an easy way to do that. This function gives you the difference between two dates as a fraction of a year. We can use the INT function to get this number back.  This rounds down a number to the next whole number. Finally, we’ll use the AVERAGE function. The AVERAGE function measures how a group of numbers in a statistical distribution tend to fall together.

⬇️⬇️ STEPS ⬇️⬇️

  • Let’s click in the cell where we want the tenure in years to show up. We clicked on E6.
  • Then we fill in with the YEARFRAC function.
  • Then we select the cell with the joining dates and add a comma.
  • Then we select the cell containing the leaving date, close with two parentheses.
=INT(YEARFRAC(C6,D6))

calculate average tenure of employees in excel with int and yearfrac

  • Once we hit Enter, Excel will calculate the number of years of service. Then we copy the formula down.

autofilling int and yearfract function to calculate average tenure of employees in excel

  • Also,let’s figure out the average tenure right now. Enter the AVERAGE function in cell F6 to determine the average tenure (E6:E11).
=AVERAGE(E6:E11)

  • After putting the range, we click ENTER and the average tenure we get is 3.3.

 Year Fractions of Employees

📕 Read More: 5 Ways to Use Formula to Find Next Month in Excel


A Simple Tenure Calculator

Readers can use the workbook for today as a calculator itself to determine the average tenure of staff. There is a separate sheet with the name “Calculator.”

There are sections for both the joining dates and the leaving dates. Place your values here. It will compute the tenure and average tenure shown in the screenshot below.

Using calculator

📕 Read More: 5 Quick Ways to Use Excel Formula to Count Days from Date


📄  Important Notes

🖊️  In Excel, the ribbon is used to access various commands. You can change a lot of things in the options dialogue window, like the ribbon, formulas, proofing, saving, and so on. So the appearance of worksheets on your device could be different from ours.

🖊️  A practice workbook is given so that you can practice yourself.

🖊️  All these exercises and tutorials are done on Microsoft Office 365. Some functions may be unavailable in previous versions.

🖊️  At the end of the Excel file, there is a sheet where they can use the calculator.


📝  Takeaway from This Article

📌  Reader will be able to calculate the average tenure of employees in Excel from tenure between the joining dates and a specific date.

📌  Readers can calculate average tenure both in years and month format.

📌  They can also know about how to use YEARFRAC Function.

📌  One will be able to find the quickest way to calculate the average tenure of employees in Excel.


Conclusion

That concludes today’s session. These are the methods for calculating the average tenure of employees in Excel. We are hopeful that this article will be extremely beneficial to you. Don’t forget to leave your thoughts and questions in the comments section and explore our website, ExcelDen, the best Excel solutions provider.


Related Articles

(Visited 80 times, 1 visits today)
Tanzim

Tanzim

My name's Tanzim. My articles are meant to help you learn more about how Microsoft Excel works and how to use its different features. I enjoy gaming and hope that some of my knowledge will help me provide you with some interesting articles despite some tiresome technical terms.

We will be happy to hear your thoughts

Leave a reply

ExcelDen
Logo