# How to Calculate Average Tenure of Employees in Excel

Get FREE Advanced Excel Exercises with Solutions!

When you are calculating tenure or average tenure, Excel can be a handy tool for you. Today I am going to show you two easy and suitable methods to calculate the average tenure of employees in Excel effectively with appropriate illustrations. For preparing examples and workbooks Iâ€™m using MS Office 2019. You can choose your version.

## Introduction to DAY and TODAY Functions

There are several functions in Excel. Gradually, you will get to know them. Today I am going to be talking to you about two basic date-time functions.

DAY functionÂ will return the value of days in a date. Let you have a date of 25 February 2021. Write the date within the DAYÂ function.

I wrote the date in the form of DD-MM-YY. Then apply the DAYÂ function to it.

It gave the day number of that date.

Letâ€™s look at another function, called DAYS.

Using the DAYS function, you can get the difference between two dates in days.

For this example, I wanted to know the day difference between 25 February 2021 and the current day. So I used another function, TODAY. I hope you remember, the TODAYÂ function returns the current date.

It provided the day difference. By the time Iâ€™m preparing this article, itâ€™s 12 October 2022.

## Tenure Basics

You will hear the term TenureÂ more often when referring to the employment of any employee. Tenure means the service period of an employee for any particular employer.

In simple language, you can compare this to the service period of an employee.

Look at the below screenshot to easily understand the tenure of an employee.

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

Letâ€™s introduce our dataset. Our dataset contains several employee tenures of XYZ group. To calculate the average tenure, we will apply the DATEDIF, TODAY, DAY, DAYS, NOW, and AVERAGE functions. Hereâ€™s an overview of the dataset for todayâ€™s task.

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

In this method, we will learn how to calculate the average tenure of employees in Excel with dynamic and specific dates. Letâ€™s follow the instructions below to learn!

#### 1.1 Dynamic Date

In this sub-method, you may need to see the tenure(service period) of your currently working employees. You will find the result by calculating the difference between their joining date and the present day. For calculating the difference, we can use the DAYSÂ function, which we discussed before starting the calculation.

But letâ€™s know about another function. Itâ€™s DATEDIF.

DATEDIFÂ function takes three parameters, start_date, end_date, and format.

`DATEDIF(start_date,end_date,format)`

Letâ€™s follow the instructions below to learn!

Steps:

• First of all, 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.
• Hence, simply press Enter on your keyboard. As a result, you will get the tenure which is the return of the DATEDIF function. The return is 21.

• Hence, autofill the functions to the rest of the cells in column D.

• Further, calculate the average tenure now. To calculate the average tenure, write down the AVERAGEÂ function in cell E5.
`=AVERAGE(D5:D9)`
• Simply press Enter on your keyboard. As a result, you will 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.
• Here I have shown the difference in months. You can get the output in years as well. Just use Y instead of M.

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

Previously, we have seen how to calculate with dynamic dates. Now letâ€™s see how you can calculate for any specific date, set manually.

Itâ€™s pretty similar to the earlier method, just in place of the TODAYÂ function, using the specific date.

Let, you calculate tenure for your ex-employees, so every employee has a joining date, and leaving date. So, to calculate tenure your formula will be like this:

`DATEDIF(Joining Date, Leaving Date, "format")`

Steps:

• First of all, select cell E5, and write down the below.
`=DATEDIF(C5,D5,"M")`
• Hence, simply press Enter on your keyboard. As a result, you will get the tenure which is the return of the DATEDIF function. The return is 24.

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

• To calculate the average tenure, write down the AVERAGE function in cell E5.
`=AVERAGE(E5:E9)`
• Hence, simply press Enter on your keyboard. As a result, you will get the tenure which is the return of the AVERAGE function. The return is 2.
• Here we got the result in monthly format.

### 2. Applying DATEDIF Function to Calculate Tenure in Year-Months Format

We calculated the tenure in months. For different occasions, situations may arise where displaying just months or years may not be ideal.

We can make a combination of year and month format. To create that first, we need to calculate the difference in years and then count the monthsâ€™ difference. To make a combination of the year and month format of the tenure using the DATEDIF function, simply repeat the sub-method 1.2. Hence, apply the DATEDIF function to get the tenure in year and month format.

Steps:

• Now, write down the DATEDIF formula in cell G5.
`=DATEDIF(C5,D5,"Y")&"Y "&DATEDIF(C5,D5,"YM")&"M "`
• Hence, simply press Enter on your keyboard. As a result, you will get the tenure in year and month format which is the return of the DATEDIF function. The return is 2Y 0M.

• Hence, AutoFill the functions to the rest of the cells in column D.
• Here Iâ€™ve inserted Joining Date and LeavingÂ Date in the placeholders for start_date and end_date and concatenated Y and MÂ to the outside of both the DATEDIF function so that the result would be presented with a unit.

## 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.

For your understanding purpose, Iâ€™ve given an example with the values of three employees. You can insert as many employees as you want.

## Conclusion

Thatâ€™s all for the session. I have tried showing you how to calculate the average tenure of employees in Excel. Hope you will find that helpful. Feel free to comment if anything seems hard to understand. You can also write your way of doing the task.

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

## What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
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

Advanced Excel Exercises with Solutions PDF