Calculate Age in Google Sheets (using the Date of Birth)

Google Sheets has some great date related formulas that can come in handy in many situations.

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

This can easily be done using the DATEDIF function in Google Sheets.

Calculate Age in Google Sheets

The way Google Sheets calculate age is by using the date of birth and the current date.

Now you can calculate the age where you get:

• The total numbers of years only
• The total number of years, months and days

Let’s see how to use formulas in Google Sheets to calculate age value.

Calculate Age – Years Only

Suppose you have a dataset as shown below:

Here is a formula that will calculate the age using the date of birth and current date:

=DATEDIF(B1,B2,"Y")

In this case, I have kept the current date in cell B2. However, you can also use the TODAY function to fetch the current date automatically. So the formula can also be:

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

Calculate Age – Years, Months, and Days

Suppose you have the dataset as shown below:

If you want to calculate not only the number of years but also the number of months and days, then we need to use a slightly longer version of the DATEDIF formula.

Let’s first see how to get the individual parts of the result (i.e., the year, month and day value).

The below formula would give you the year value:

=DATEDIF(B1,B2,"Y")

The below formula would give you the month value:

=DATEDIF(B1,B2,"YM")

The below formula would give you the day value:

=DATEDIF(B1,B2,”MD”)

Now we can combine this entire formula to get the age that includes the year, month and day value. Below formula can get this done:

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

As mentioned, you can also use this technique to find the project duration. For example, below I have used a similar formula to show how many years, months, and days each project activity would take to complete.