How to Calculate Age in Google Sheets (Easy Formulas)

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. If you search the Internet, you might find various techniques to do this.

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.

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.

In this Google Sheets tutorial, I will show you how to quickly calculate age in Google Sheets using the date of birth using formulas.

Also, you can calculate age to show you how many years have passed in the date of birth, and even get more specific about how many years, months and days have passed since the date of birth of the person.

Calculate Age in Google Sheets – 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 date of birth 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 using 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

Calculate Age in Google Sheets – 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.

And 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.

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

Sumit

Sumit

Sumit is a Google Sheets and Microsoft Excel Expert. He provides spreadsheet training to corporates and has been awarded the prestigious Excel MVP award by Microsoft for his contributions in sharing his Excel knowledge and helping people.

7 thoughts on “How to Calculate Age in Google Sheets (Easy Formulas)”

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

      Reply
  1. 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
  2. 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
  3. 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

Leave a Comment

Related Posts

Disclosure: Spreadsheet Point is supported by its audience. When you purchase through links on our site, we may earn an affiliate commission.