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 someone's age in Google Sheets using their 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:

Calculate Age in Google Sheets - Dataset

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

=DATEDIF(B1,B2,"Y")

Calculate Age in Google Sheets - datedif formula age in years only
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")
You can also use this technique to calculate the time taken by projects or tenure of someone's service based on their joining date and retirement date.

Calculate Age – Years, Months, and Days

Suppose you have the dataset as shown below:

Calculate Age in Google Sheets - Dataset

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

Calculate Age in Google Sheets - Year Value Only

The below formula would give you the month value:

=DATEDIF(B1,B2,"YM")

Calculate Age using DatedIF function - Month Value Only

The below formula would give you the day value:

=DATEDIF(B1,B2,”MD”)

Day portion while getting the age in Google Sheets

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"

Age in full text version

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.

Calculate project timeline using DatedIf formula

You May Also Like the Following Google Sheets Tutorials:

>