When you are going to calculate tenure or average tenure, Excel can be a handy tool for you. Today I am going to show you how to calculate average tenure of employees in Excel. For preparing examples and workbooks I’m using Excel 2019. You can choose your version.

First things first, let’s get to know about the worksheet used as the base of examples for today’s session.

There are two tables in the sheet. **Current Employee Table** and **Ex Employee Table**. **Current Employee Table** and **Ex Employee Table** will be used to let you understand how to calculate using dynamic dates and specific static dates respectively.

## Practice Workbook

You can download the practice workbook from the link below

## Basics you may need to know

### Basic Date Functions

There are a number of 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 days in a date. Let you have a date of 25 February 2021. Write the date within **DAY()** function.

I wrote the date in the form of **DD-MM-YY**. Then apply the **DAY()** function on it.

It gave the day number from 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 **TODAY()**. Hope you remember, **TODAY()** function returns the current date.

It provided the day difference. By the time I’m preparing this article, it’s 22 June 2021.

### Tenure Basics

You will hear the term “* Tenure*” more often while referring to the employment of any employee. Tenure basically 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.

## Calculate Average Tenure of Employees in Excel

### Calculate for Dynamic Dates (using TODAY())

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 have discussed prior to 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 write the formula in Excel following this syntax.

In the placeholders I’ve inserted **Joining Date**, **TODAY()** and **“M”**.

Since we want to see the result on the current day, it’s ideal to calculate using **TODAY() **function. If you use **TODAY()** function, when you open this workbook a couple of days/ months (whenever you like), it will show the result based on that day.

We put **M** for getting the difference in *months*.

Do the same for the rest of the rows. ** **

Let’s calculate the average tenure now. To calculate the average, all you need to do is use the **AVERAGE()** function.

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 **

Look, it gave the difference in *years*. Since I’m preparing this tutorial for June 2021, there is no year difference between the dates.

### Calculate for Static Dates

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 **TODAY()** function, use 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”)**

Here I’ve written the formula in the Excel sheet, while my desired output format was *month***. **So, I used **“M”**. As shown earlier, you can use **Y** (for years).

Do the same for the rest of the rows, or exercise the Excel **AutoFill** feature.

Now to calculate the average, use the **AVERAGE()** function as before.

Within the **AVERAGE()** function I gave the range of values. While you are working on a large dataset, it’s beneficial to use range rather than comma separated values.

Similar to the previous section example, you choose *years* format as well.

Here we got the result in years format. Use **AVERAGE()** function to get the average value.

### Calculate Tenure in Year-Months Format

We calculated tenure in months. If you remember, for the first table when I showed calculating differences in year forms, it gave us *0*.

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.

**DATEDIF()** function will be in use again.

This time we need two** DATEDIF()** function

First one will calculate date difference in* years* format, syntax for this one will be

**DATEDIF(start_date,end_date,”Y”)**

Second one will calculate the date difference in *months* format, regardless of the year. The syntax will be

**DATEDIF**(

**start_date,end_date,”YM”**)

Write a formula using these syntax and use “*&**” *to concatenate the individual parts. Concatenation means addition. So, formula, as a whole, will be like

**DATEDIF(start_date,end_date,”Y”) & “Y ” & DATEDIF(start_date,end_date,”YM”) & “M “**

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.

For calculating average tenure for this format, we have to utilize advanced excel methods that will be discussed another time.

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

## 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 **Joining date**, **Leaving date**. Insert your values. It will calculate **Tenure** and the **Average Tenure**.

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