How to Calculate Tenure in Google Sheets (An Easy Guide)

A common misunderstanding that many new users of spreadsheets have is how age is calculated. One might think that a simple subtraction will get the work done.

While subtraction between years can be done, Google Sheets will not consider the months into account. This means that by subtracting dates, let’s say between 5/25/2018 and 3/13/2021, Google sheets will return a 3-year difference, even though 3 years have not passed.

arithmetic calculations give wrong answers for tenure in google sheets

So, in this article, we’ll show you how to properly calculate the age, more specifically the age of service or tenure in Google Sheets.

Let’s get started.

How to Calculate Tenure in Google Sheets

Calculate Tenure in Years (or Years of Service) in Google Sheets

The easiest way to calculate date-related differences is to use the DATEDIF function.

The DATEDIF function syntax:

DATEDIF(start_date, end_date, unit)

datedif function syntax

As you can see, the input fields are quite easy to understand. So, I bring your attention to the unit field. Here, you can input the type of output you desire: Days, Months, or Years.

Here is a list of what inputs the unit field takes:

  • “Y”: Gives the total number of whole years between the start date and end date.
  • “M”: Gives the total number of whole months between the start date and end date.
  • “D”: Gives the total number of days between the start date and end date.
  • “MD”: Gives the number of days between the start date and end date without counting the months or years completed.
  • “YM”: Gives the number of whole months between the start date and end date without counting the months and years completed.
  • “YD”: Gives the number of days between the start date and end date, assuming that the date range is no more than one year apart.

Thus, our formula to calculate age or tenure in years will be:

=DATEDIF(B2,B3,"Y")

getting the correct age or tenure with the datedif function

For a more practical scenario we have created the following dataset:

practical dataset to calculate tenure in google sheets

The first tenure is already calculated, but what to do for “Active” employees?

Active means that they are currently employed, thus we calculate our end date to be today. Which can be represented by the TODAY function.

=DATEDIF(C4,TODAY(),"Y")

calculating tenure till today by using the today function

But having two different functions in a single column is not intuitive nor is it a good idea. So, we have created a universal formula that can be applied to the whole column with the help of the IF function.

=IF(D3="Active",(DATEDIF(C3,TODAY(),"Y")),(DATEDIF(C3,D3,"Y")))

how to dynamically calculate tenure in google sheets using if and dated if functions

Alternative: The YEARFRAC Function

The YEARFRAC function returns the number of years between two dates in Google Sheets. The fact that it can also return fractional years will be useful to calculate tenure in Google Sheets.

On its own, the YEARFRAC function gives is the following result:

=YEARFRAC(B2,B3)

tenure in fractional years

It did give us the years, but in fractions/decimals. To get an integer year tenure, we must enclose the formula in the INT function.

=INT(YEARFRAC(B2,B3))

how to calculate tenure in years in google sheets using the yearfrac function

Practical dataset: Calculate tenure in years in Google Sheets using YEARFRAC function:

=IF(D3="Active",INT(YEARFRAC(C3,TODAY())),INT(YEARFRAC(C3,D3)))

using yearfrac to calculate tenure in a practical setting

Calculate Tenure or Age of Service in Years and Months in Google Sheets

In a professional setting, it is better to present the tenure of an employee not only in years but also in months covered.

Recall our list of units for the DATEDIF function. The “YM” unit will present us with the current number of months covered in the running year.

DATEDIF(start_date,end_date,"YM")

Unfortunately, we hit another snag at this point. The DATEDIF function cannot output both the year and month data simultaneously. We have to put the two separate outputs in a single cell for presentation.

So, our formula looks something like this:

=DATEDIF(C3,D3,"Y")&" Year(s) and "&DATEDIF(C3,D3,"YM")&" Month(s)"

how to calculate tenure of years and months in google sheets

Using ampersand (&) we were able to concatenate the two results of DATEDIF into one cell to a more professional-looking outcome.

Extra: Calculate Tenure in Years, Months, and Days

Why not go a step further and add the number of covered days while we are at it?

Since we are looking for the number of days after a month is completed, the unit field will have “MD”.

Our formula:

=DATEDIF(C3,D3,"Y")&" Year(s) and "&DATEDIF(C3,D3,"YM")&" Month(s) "&DATEDIF(C3,D3,"MD")&" Day(s)"

tenure with years, months and days in google sheets

Final Words

Calculating tenure in Google Sheets is quite simple as you have seen in this article. Only that you have to use a date function like DATEDIF to get the job done. Regula arithmetic functions like minus will not work correctly with dates.

Feel free to leave any queries or advice you might have in the comments section below. Or go ahead and have a look at our other date-related articles to make your Google Sheets experience simpler.

Related Articles for Reading

Mehrab Imtiaz

Mehrab Imtiaz

Mehrab Imtiaz is a Technical Writer for officewheel.com specializing in everything Google Sheets. Mehrab has always had a profound passion for writing and with his experience with MS Excel and Google Sheets throughout his career, it seems to be a great match when it comes to dishing out tips and tutorials for all to enjoy.

We will be happy to hear your thoughts

Leave a reply

OfficeWheel
Logo