Tenure Formula in Excel
When referring to any employee’s employment, the term “Tenure” will be used more frequently. The term “tenure” refers to an employee’s service period for any specific employer. In simple words, Tenure means the service period of an employee.
When it comes to calculating tenure or average tenure, Excel can be helpful for us. In this tutorial, we will discuss how to use Excel to compute average employee tenure.
If we’re in charge of keeping track of personnel in our department or firm, we might be wondering if we can use Excel to
compute the months of tenure for those individuals based on their start date. This can be done very easily.
For example, suppose column B comprises the starting date for a list of employees. In order to determine each employee’s tenure, enter the following formula into column C:
In this formula, the DATEIF function computes the difference between a starting date and an ending date. The letter “M” in the formula denotes that we want the result in completed months.
If we want to calculate the average tenure for our group of employees, simply put the following formula at the bottom of column B:
How to Calculate the Tenure for Staff in Months or Years in Excel
Calculating the tenure for the employees in their firm or department is a normal task for HR assistants or department secretaries. Therefore, acquiring the skill of calculating tenure in Excel is a must. In this tutorial, we will discuss how to calculate tenure using a simple formula. The final value can be expressed in months or years, depending on our needs.
Let’s understand how to calculate the tenure for staff with the help of the following example:
Example 1: Calculate the Tenure for Staff in Months
We can use the DATEIF function to calculate the tenure for a staff. This function returns the Years/Months/Dates between two dates. The general formula for DATEIF function is:
Suppose we have a list of employees in which some of the employees are already quit, and some are still working.
Below is the list of employees with their Entry Date and Leave Date.
- In order to calculate the tenure for staff in month, we have to enter the following formula in cell D2 =DATEDIF(B2,NOW(),”M”). In this formula, B2 is the entry date. Because there is no end date or leave date, so we use NOW() to calculate the months between the entry date and now, “M” stands for month unit.
- Then we have to click Enter so that we can get the output.
- We can use the formula =DATEDIF(B2,C2,”M”) if there is an end date parameter, such as for Yash, leave date is 05-04-2021.
- Next, we have to click Enter so that we can get the output.
- Lastly, we will calculate the tenure for the remaining cells using similar formula as we used previously.
Example 2: Calculate the Tenure for Staff in Years
In this example, we will use the same dataset. This example is just like the previous one except that we will calculate the tenure from M to Y in this case.
To calculate the tenure for staff in years, we have to follow the following steps:
- First, we have to enter the following formula into cell
- Then we have to click Enter in order to get the output.
- Now, we enter the below formula in cell
- Then, have to click Enter in order to get the output.
- Lastly, we have to calculate the tenure for the remaining cells using similar formula as we used previously.
Example 3: Calculate the Tenure for Staff in Years and Months
In this example again, we will take the same dataset as we have taken in the first and second examples. In this, we will calculate the tenure for staff in years and months.
We have to follow the following steps in order to calculate the tenure for staff in years and months:
- First, we have to enter the below formula into cell
- Then, we have to click Enter in order to get the output.
- Next, we have to enter the below formula in cell
- Then, we have to press Enter in order to get the output.
- Lastly, we have to calculate the tenure for the remaining cells using similar formula as we used previously.
