How to calculate age from date of birth with formulas in Google Sheets

If you have a list of people with their date of birth mentioned on Google Sheets, and you need to find their age, then this article will be helpful. This discussion is explained under the assumption that the dates are written dd/mm/yyyy format or dd-mm-yyyy format. It has been tested on Google Sheets and not any other similar software.

Calculate age from date of birth with formulas in Google Sheets

We can calculate the age of people on a Google Sheet page as follows:

  1. Using the DATEDIF formula
  2. Using the Array formula

1] Using the DATEDIF formula

The syntax for the DATEDIF formula is as follows:

=IF(<position of first cell in the column>,DATEDIF(<position of first cell in the column>,TODAY(),"Y"),"")

Where <position of first cell in the column> is the cell number of the first cell with the date of birth mentioned in the column of date of birth.

Eg. If you are creating the list of ages of the individuals in column C and the cell number of the first cell in the column of cells mentioning the dates of birth is B3, the formula would become as follows:

=IF(B3,DATEDIF(B3,TODAY(),"Y"),"")

Calculate age from date of birth with formulas in Google Sheets

You would have to copy-paste this formula to cell C3 and press Enter to execute the operation. Click on any other blank cell and then click back on C3. A dot would appear on the right-bottom of the C3 cell. Use it to pull down the formula to corresponding last cell in column B, which mentions the date of birth. Eg. If the last cell in column B which mentions the date of birth is B8, pull the formula C8.

List of ages by DATEDIF formula

Interestingly, the DATEDIF formula has an option if you wish to get the exact number of years, months, and days corresponding to the dates of birth. The syntax would be:

=DATEDIF(<position of first cell in the column>,TODAY(),"Y")&" Years, "&DATEDIF(<position of first cell in the column>,TODAY(),"YM")&" Months, "&DATEDIF(<position of first cell in the column>,TODAY(),"MD")&" Days"

Eg. In the example mentioned above, the formula would become as follows:

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

 

Get the exact number of years, months, and days corresponding to the dates of birth

Some users might find it difficult to pull the formula across cells.

2] Using the Array formula

Unlike the DATEDIF formula, the Array formula requires you to mention all the details in the formula itself, thus making it easier to use.

The syntax for the Array formula is as follows:

=ArrayFormula(int(yearfrac(<position of first cell on which date of birth is mentioned>:<position of last cell on which date of birth is mentioned>,today(),1)))

Where <position of first cell in the column> is the cell number of the first cell with the date of birth mentioned in the column of date of birth and <position of the last cell in the column> is the cell number of the last cell with the date of birth mentioned in the column of date of birth.

Eg. If the dates of birth are listed in column B from B3 to B8, the formula will become:

=ArrayFormula(int(yearfrac(B3:B8,today(),1)))

Age by array formula

If you wish to create the formula for an infinite number of rows, the syntax would be:

=ArrayFormula(if(len(<position of first cell on which date of birth is mentioned>:<Row in which dates of birth are listed>),(int(yearfrac(<position of first cell on which date of birth is mentioned>:<Row in which dates of birth are listed>,today(),1))),))

Eg. In the case mentioned above, the formula would become as follows:

=ArrayFormula(if(len(B3:B),(int(yearfrac(B3:B,today(),1))),))

The procedures mentioned in this post would be especially helpful for those who are managing employees, students, sports teams, etc.

Hope it helps!

Posted by on , in Category General with Tags
Karan Khanna is a passionate Windows 10 user who loves troubleshooting Windows problems in specific and writing about Microsoft technologies in general.

Leave a Reply

Your email address will not be published. Required fields are marked *


1 + 9 =