Tanzim
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.
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.
- Then simply press the Enter key on your keyboard. Thus, you will receive the DATEDIF function’s return. The result is 3.
- Autofill the remaining column E cells as well.
- Once that is done, figure out the average tenure. In cell F6, enter the AVERAGE function to determine the average tenure.
- Now press the Enter key on your keyboard. The tenure, which is the return of the AVERAGE function, is 3.
🔨 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
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.
- 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.
- Now figure out the average tenure. In cell E6, enter the AVERAGE function to determine the average tenure.
- Now press the Enter key on your keyboard. The tenure, which is the return of the AVERAGE function, is 5.8.
🔨 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
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.
- 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.
- Autofill the remaining column E cells as well.
- Now figure out the average tenure. In cell F6, enter the AVERAGE function to determine the average tenure.
- 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.
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.
- Once we hit Enter, Excel will calculate the number of years of service. Then we copy the formula down.
- Also,let’s figure out the average tenure right now. Enter the AVERAGE function in cell F6 to determine the average tenure (E6:E11).
- After putting the range, we click ENTER and the average tenure we get is 3.3.
📕 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.
📕 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.
