How to Calculate Age in Google Sheets (Easy Formulas)

There are numerous situations where you might need to calculate the age of a group of people based on their date of birth. However, the process is not as simple as subtracting the birth year from the current year. In this Google Sheets tutorial, I will show you how to calculate age using formulas, while considering all the factors involved.

Calculate Age in Google Sheets – Number of Years

Suppose you have a dataset with the date of birth in one cell and the current date in another. There are two formulas you can use to calculate the age in years.

Using the DATEDIF Formula

The DATEDIF function is the first formula you can use. It calculates the total number of years that have elapsed between the two given dates. Here’s how to use it:

=DATEDIF(B1,B2,"Y")

You can also use the TODAY function within the formula to automatically calculate the current date:

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

Using the YEARFRAC Formula

Another way to calculate the age in years in Google Sheets is by using the YEARFRAC function. It gives you the number of years (including fractional years) between two given dates. Here’s the formula:

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

Remember, we only want the integer part of the age, so we use the INT function.

Calculate Age in Google Sheets – Number of Years, Months, and Days

If you want to calculate the age in years, months, and days, you can use the DATEDIF function.

Using the DATEDIF Formula

To calculate the age in years, use the formula:

=DATEDIF(B1,B2,"Y")

To get the total number of months that have passed (not counting completed years), use the formula:

=DATEDIF(B1,B2,"YM")

To calculate the total number of days that have elapsed (not counting completed months), use the formula:

=DATEDIF(B1,B2,"MD")

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

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

Remember, you can also use the TODAY function instead of using a cell reference for the current date.

These formulas can also be used to calculate age in Excel using the date of birth.

I hope you found this tutorial useful! For more Google Sheets tutorials, check out Mr Reviews.