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.
How to calculate Age in Google Sheets from Date of Birth
We can calculate the age of people on a Google Sheet page as follows:
- Using the DATEDIF formula
- 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"),"")
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.
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"
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)))
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.
In case you want to calculate the age containing only year (and no months and days) for a single cell only, then you can use the basic YEARFRAC function with cell number and current date. So, let’s say you want to calculate the age from the date of birth present in cell A5, then the formula will be:
=int(YEARFRAC(A5, today()))
Add this formula in a cell and hit the Enter key to get the result.
That’s it!
How do you calculate years between two dates in Google Sheets?
If you want to calculate the number of whole years between two dates in Google Sheets, then use the DATEDIF function along with cell #1 and cell #2 with year unit (Y). So, let’s say you have a date in cell C4 and the second date in cell C5, then to calculate the years between those two dates, the formula will be:
=DATEDIF(C4, C5, "Y")
What is the formula age from date of birth in Excel?
To calculate the whole age from the date of birth in Excel, you can use the YEAR and NOW functions. As an example, if the date of birth is added in cell A7, then to calculate the total age from that date of birth, use the following formula:
=(YEAR(NOW())-YEAR(A7))
Make sure the cell where the date of birth is present displays a number (or formatted as a number or General), otherwise, the output won’t come.
Hope it helps!
