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 average tenure of employees in Excel** effectively with appropriate illustrations. For preparing examples and workbooks I’m using Excel 2019. You can choose your version.

**Table of Contents**hide

**Download Practice Workbook**

You can download the practice workbook from the link below.

**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**, This function will return the value of

**. Let you have a date of**

__days in a date__**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

**.**

__day difference between two dates__For this example, I wanted to know the ** day difference** between

**25 February 2021**and the

**current day**. So I used another function,

**. I hope you remember, the**

__TODAY()__**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.

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

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

**functions. Here’s an overview of the dataset for today’s task.**

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

`=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** - Since we want to see the result on the current day, it’s ideal to calculate using the
**TODAY()**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__**E5**.

`=AVERAGE(D5:D9)`

- 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**. - 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 are calculating tenure for your ex-employees, so every employee has a joining date, and leaving date. So, to calculate tenure your formula will be like

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

Let’s follow the instructions below to learn!

**Steps:**

- First of all, select cell
**E5**, and write down the below**DATEDIF**

`=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**.

** **

- After that, calculate the average tenure now. To calculate the
**average tenure**, write down__the AVERAGE() function__**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
**months**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. Let’s follow the instructions below to learn!

**Steps:**

- Now, write down the
**DATEDIF**function 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**, **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.

**Further Readings**

__Excel Formula to Calculate Number of Days Between Today & Another Date (6 Quick Ways)____Excel formula to Calculate Age on a Specific Date____How to Subtract/Minus Days from Today’s Date in Excel (4 Simple Ways)____Excel Formula to Count Days from Date____How to Calculate Overdue Days in Excel (using Functions)____How to Add/Subtract Years to a Date in Excel____How to calculate working days in Excel excluding weekends & holidays__