Search
Close this search box.

How to Calculate Age in Google Sheets (2 Easy Methods)

Fact Checked By Jim Markus

To calculate age in Google Sheets, use the DATEDIF or YEARFRAC formulas. I’ll cover each in detail below. Follow along for screenshots and full formula explanations.

Formulas that Calculate Age in Google Sheets

There are a multitude of situations when you might need to calculate the age of a group of people based on their date of birth. Unfortunately, it’s not as simple as just taking the birth year and subtracting it from the current year. There are a lot of things you need to consider.

There are two ways to calculate ages in Google Sheets.

For example, say it is 29th Jan 2020 and you have to find the age of a person born on 8th Nov 2007. Simply subtracting 2007 from 2020 will give you 13 years. However, you need to consider the fact that the person was born in November, so technically, he/ she has not yet completed 13 years.

So let’s talk about counting the number of years. Then, I’ll cover finding days, months, and years.

Counting the Number of Years

Suppose you have the dataset as shown below where you have the date of birth in cell B1 and the current date in cell B2. Note that I have used the TODAY function to get the current date in cell B2.

Data to calculate Age

Now there are two formulas you can use to calculate the age in Google Sheets in years (i.e., to get the total number of years that have elapsed between the two given dates).

The first formula that you can use to do this is the DATEDIF formula (called the Date Difference formula), and the second one is the YEARFRAC function.

Using the DATEDIF Formula

Let me first show you how to use the DATEDIF function.

The below formula will calculate the age in the total number of years between the two given dates:

=DATEDIF(B1,B2,"Y")

 

Calculating Age in Google Sheets using DATEDIF

The above formula takes three arguments:

  • Start Date – this is the date of birth in our example
  • End Date – this is the current date in this example
  • Unit – this is the argument that tells the DATEDIF function what needs to be calculated. In this example, I have used “Y”, which tells the formula to only calculate the total number of years that have elapsed between the two given dates.

Note that I have used the current date as a cell reference (where I already have the current date in cell B2). But you can also use the below formula to do this (where the current date is automatically calculated using the TODAY function within the formula itself):

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

Also, let me give you all the other units you can use in the third argument of the DATEDIF function (we will be using these in the next section where we need to calculate age in years months, and dates in Google Sheets).

Below are all the values you can use in the unit argument in the DATEDIF function;

  • “Y” – this will give you the total number of years that have passed between the two given dates
  • “M” – this will give you the total number of months that have passed between the two given dates
  • “D” – this will give you the total number of days that have passed between the two given dates
  • “MD” – this will give you the total number of days that have passed between the two given dates but doesn’t count the ones in the Years and Months that have been completed.
  • “YM” – this will give you the total number of months that have passed between the two given dates but doesn’t count the ones in the years and months that have been completed.
  • “YD” – this will give you the total number of days that have passed between the two given dates but doesn’t count the ones in the years that have been completed.

These will become a lot more clear when we look at more examples in the next section.

Using the YEARFRAC Formula

Another way to calculate the age in years in Google Sheets is by using the YEARFRAC function.

Below is the formula that will give you age in years when you have the date of birth and the current date:

=INT(YEARFRAC(B1,TODAY()))

Calculating Age in Google Sheets using YEARFRAC

The YEARFRAC function will give you the number of years (including fractional years), between two given dates. Since we only want to get the age in years, I have used the INT function, which only gives us the integer part of the age.

Note: When working with dates in Google Sheets, if you want a specific date in a cell, it’s best to use the DATE function to get it. This will prevent any chances of error – most common could be having a date that Google Sheets doesn’t recognize as a date

Counting the Number of Years, Months, and Days

If you only want to calculate the age in years, you can use the formulas covered above. But to get the age in years as well as in months and days would need slightly different formulas.

Again, you can use the DATEDIF function to calculate the age which has been broken down by years, months and days.

Using the DATEDIF Formula

Suppose you have the same dataset and you want to calculate the age in years as well as months and days.

The below formula will give you the age in years (i.e., the total number of years that have passed in between the two given dates):

=DATEDIF(B1,B2,"Y")

Years Elapsed between two dates

Now, to get the total number of months that have passed (not counting the completed years), you can use the below formula:

=DATEDIF(B1,B2,"YM")

Months Elapsed between two dates

This returns 8 in our example, as 8 months have passed after 41 completed years.

The below formula will give you the total number of days that have elapsed (not counting the completed months):

=DATEDIF(B1,B2,"MD")

Days elapsed between two dates

And if you want to combine all these values to show the age in years, months and days, you can use the below formula:

=DATEDIF(B1,B2,"Y")&" Years "&DATEDIF(B1,B2,"YM")&" Months "&DATEDIF(B1,B2,"MD")&" Days"

Full age in years months and days

Note that I have used a cell reference in the formula above to get the current date, but you can also use TODAY function to fo this instead of using B2.

So there are the formulas you can use to calculate age in Google Sheets. If you only need the age in years, you can use DATEDIF or YEARFRAC, but if you need it to be broken down by years, months, and days, you need to use the DATEDIF function only.

Video Guide: Calculating Ages in a Spreadsheet

Here’s my video that shows how to use the two most common formulas for calculating age (in years or in days, months, and years).

Conclusion

I hope you found this tutorial useful! Note that you can also use these same formulas if you want to calculate age in Excel (using the date of birth).

While I have used these formulas to calculate age in Google Sheets, you can also use these to calculate years/months/dates that have elapsed between any two dates. Be careful when using the functions with IMPORTRANGE as it can sometimes cause errors.. This could help you get a better idea of the project duration or while forecasting/planning using future dates.

You may also like the following Google Sheets tutorials:

Most Popular Posts

8 thoughts on “How to Calculate Age in Google Sheets (2 Easy Methods)”

  1. The key to all of this is making sure that the TODAY function to get the date of birth in cell B2 is used.

    =(today())

    I suspect that most people breeze through all of the instructions (just as I did) and miss this VERY important point!

    Reply
    • The TODAY function doesn’t calculate the date of birth, it gets the current date. That is a mistake in the text.

      Reply
  2. This is a very helpful formula. I have a few add-ons after my trial and error attempts:

    1. To calculate the age on a particular date instead of using the “TODAY” option, mention the specific date within double quotes.

    e.g. To calculate the age as on 12/31/2030 where DOB is 01/01/1950 use the formula =DATEDIF(“01/01/1950″,”12/31/2030″,”Y”). Note: The date format is set as “mm/dd/yyyy”, however it can be changed from the cell format option ans used accordingly in the formula.

    2. I believe the field where age is calculated should be set as either “Automatic” or “Numeric”.

    Reply
  3. Works a treat – so happy to have this up and running. I got caught out as the format of the cell was incorrectly set, but a quick change to Automatic/Numeric did the trick> Thank you

    Reply
  4. Works well, thank you!

    I did encounter one issue though, I am using it to calculate how old someone would be if alive today. I used
    =INT(YEARFRAC(B1,TODAY()))
    and found it doesn’t work with dates prior to 1900! Any thoughts on that?

    Reply
    • This is actually a really weird story, but I’ve got it figured out.

      The first argument in YEARFRAC calls the date in cell B1. However, dates in Google Sheets (like dates in Excel) are actually formatted as numbers.

      To determine the number, Google Sheets calculates the date in relation to December 30, 1899. Days before this will appear as negative numbers, and negative numbers aren’t allowed in the YEARFRAC function’s first argument.

      You can get around the issue by using the other formula I mentioned in the video. It’s =DATEDIF(B1,B2,”Y”)

      That gives you the same result you’d expect from the INT version of your YEARFRAC formula.

      Thanks for the question!

      Reply

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Related Posts

Thanks for visiting! We’re happy to answer your spreadsheet questions. We specialize in formulas for Google Sheets, our own spreadsheet templates, and time-saving Excel tips.

Note that we’re supported by our audience. When you purchase through links on our site, we may earn commission at no extra cost to you.

Like what we do? Share this article!