Table of Contents
To calculate tenure in Google Sheets, you can use the DATEDIF function, which calculates the difference between two dates in years, months, or days. First, enter the employee’s start date in one cell and their end date (either current date or date of leaving) in another cell. Then, use the DATEDIF function to determine the difference in years or months. This will give you the tenure of the employee in the desired unit of time. You can also use the TODAY function to automatically update the tenure whenever the sheet is opened. This method allows for an accurate and efficient calculation of an employee’s tenure in Google Sheets.
Calculate Tenure in Google Sheets
You can use one of the following formulas to calculate employee tenure in Google Sheets:
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.1694 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 Google Sheets:
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.1694 years.
- Bob has a tenure of 3.8944 years.
- Chad has a tenure of 9.2805 years.
And so on.
Note: You can find the complete documentation for the Google Sheets YEARFRAC function .
The following tutorials explain how to perform other common tasks in Google Sheets:
Cite this article
stats writer (2024). How do I calculate tenure in Google Sheets?. PSYCHOLOGICAL SCALES. Retrieved from https://scales.arabpsychology.com/stats/how-do-i-calculate-tenure-in-google-sheets/
stats writer. "How do I calculate tenure in Google Sheets?." PSYCHOLOGICAL SCALES, 23 Jun. 2024, https://scales.arabpsychology.com/stats/how-do-i-calculate-tenure-in-google-sheets/.
stats writer. "How do I calculate tenure in Google Sheets?." PSYCHOLOGICAL SCALES, 2024. https://scales.arabpsychology.com/stats/how-do-i-calculate-tenure-in-google-sheets/.
stats writer (2024) 'How do I calculate tenure in Google Sheets?', PSYCHOLOGICAL SCALES. Available at: https://scales.arabpsychology.com/stats/how-do-i-calculate-tenure-in-google-sheets/.
[1] stats writer, "How do I calculate tenure in Google Sheets?," PSYCHOLOGICAL SCALES, vol. X, no. Y, ص Z-Z, June, 2024.
stats writer. How do I calculate tenure in Google Sheets?. PSYCHOLOGICAL SCALES. 2024;vol(issue):pages.