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.

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")

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()))

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.

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")

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")

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")

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"

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

You may also like the following Google Sheets tutorials:

• How to Calculate Days Between Two Dates in Google Sheets
• Count Cells IF NOT Blank (Non-Empty cells) in Google Sheets
• How to Add Time in Google Sheets
• How to Round Numbers in Google Sheets