Table of Contents
Calculating tenure in Excel is a simple and efficient way to determine the length of time an employee has been with a company. This can be done by using the DATEDIF function, which calculates the difference between two dates in years, months, or days. By inputting the employee’s start date and the current date, the DATEDIF function will provide the tenure in the desired time unit. For example, if an employee’s start date is January 1st, 2015 and the current date is October 1st, 2021, the DATEDIF function will return a tenure of 6 years and 9 months. This method is useful for HR professionals and managers to track employee tenure and make informed decisions regarding promotions, raises, and other employment matters.
Calculate Tenure in Excel (With Example)
You can use one of the following formulas to calculate employee tenure in Excel:
Formula 1: Calculate Tenure in Years and Months (e.g. 14 years, 2 months)
=DATEDIF(B2,C2,"y") & " years , "& DATEDIF(B2,C2,"ym") & " months"
Formula 2: Calculate Tenure in Years as Decimal (e.g. 14.16944 years)
=YEARFRAC(B2, C2)
Both formulas assume that the start date is in cell B2 and the end date is in cell C2.
The following examples show how to use each formula in practice with the following dataset in Excel:
Example 1: Calculate Tenure in Years and Months
We can type the following formula into cell D2 to calculate the tenure for the first employee in terms of years and months:
=DATEDIF(B2,C2,"y") & " years , "& DATEDIF(B2,C2,"ym") & " months"
We can then drag and fill this formula down to each remaining cell in column D to calculate the tenure for each employee:
The values in column D display the tenure for each employee in terms of years and months.
If you simply want to display the tenure in terms of total days, you can use the following formula instead:
=DATEDIF(B2, C2, "d")
This will display the tenure in terms of days instead of years and months.
Example 2: Calculate Tenure in Years as Decimal
We can type the following formula into cell D2 to calculate the tenure for the first employee in terms of years as a decimal:
=YEARFRAC(B2, C2)
We can then drag and fill this formula down to each remaining cell in column D to calculate the tenure for each employee:
The values in column D display the tenure for each employee in terms of years as a decimal.
For example:
- Andy has a tenure of 14.169 years.
- Ben has a tenure of 3.894 years.
- Charles has a tenure of 9.281 years.
And so on.
Note: You can find the complete documentation for the Excel YEARFRAC function .
The following tutorials explain how to perform other common tasks in Excel:
Cite this article
stats writer (2024). How can I calculate tenure in Excel? Can you provide an example?”. PSYCHOLOGICAL SCALES. Retrieved from https://scales.arabpsychology.com/stats/how-can-i-calculate-tenure-in-excel-can-you-provide-an-example/
stats writer. "How can I calculate tenure in Excel? Can you provide an example?”." PSYCHOLOGICAL SCALES, 26 Jun. 2024, https://scales.arabpsychology.com/stats/how-can-i-calculate-tenure-in-excel-can-you-provide-an-example/.
stats writer. "How can I calculate tenure in Excel? Can you provide an example?”." PSYCHOLOGICAL SCALES, 2024. https://scales.arabpsychology.com/stats/how-can-i-calculate-tenure-in-excel-can-you-provide-an-example/.
stats writer (2024) 'How can I calculate tenure in Excel? Can you provide an example?”', PSYCHOLOGICAL SCALES. Available at: https://scales.arabpsychology.com/stats/how-can-i-calculate-tenure-in-excel-can-you-provide-an-example/.
[1] stats writer, "How can I calculate tenure in Excel? Can you provide an example?”," PSYCHOLOGICAL SCALES, vol. X, no. Y, ص Z-Z, June, 2024.
stats writer. How can I calculate tenure in Excel? Can you provide an example?”. PSYCHOLOGICAL SCALES. 2024;vol(issue):pages.