In this article, we will explore the different methods to calculate age from birthdates in Google Sheets. Whether you need to calculate the age in years only or in years, months, and days, we’ve got you covered. We will also introduce a user-defined function for quick and easy age calculations.
Calculate Age from Birthdates in Years Only in Google Sheets
Non-Array Formulas:
To calculate the age in years only, you can use either the DATEDIF or YEARFRAC formula. Let’s take a look:
Cell A1 contains the birthdate (e.g., 31/12/2000). In cell B1, use the following formula:
=INT(YEARFRAC(A1,TODAY(),1))
In the above formula, the “1” represents the day count conversion. For a more detailed explanation, please refer to our date functions guide.
Alternatively, you can use the DATEDIF formula:
=DATEDIF(A1,TODAY(),"Y")
Array Formulas:
Now, let’s say you want to calculate the ages of a group of people, such as students, players, or team members. You can use an array formula for this purpose. Here’s how:
=ArrayFormula(IFERROR(IF(DATEVALUE(A3:A7), INT(YEARFRAC(A3:A7,TODAY(),1))))
Alternatively, you can use the DATEDIF formula:
=ArrayFormula(IFERROR(IF(DATEVALUE(A3:A7), DATEDIF(A3:A7,TODAY(),"Y"))))
Formula Explanation:
To identify whether a cell contains a valid date, we use the DATEVALUE function. It converts the date in a cell into a date value or returns an error if the cell does not contain a date value. If a cell contains a date value, we want to use the DATEDIF or YEARFRAC formula to calculate the age. That’s why we use the IF function in the formula. The IFERROR function helps remove any errors returned by DATEVALUE. Since we are using an array (A3:A7) within the non-array formulas, we must use the ArrayFormula function.
Calculate Age from Birthdate in Year, Month, and Day in Google Sheets
There are two methods to calculate the age in years, months, and days. You can either use a formula approach or our custom user-defined function.
Formula Approach
The DATEDIF function is more flexible for calculating the age from a birthdate in Google Sheets. With this function, you can obtain the age in the year, month, and day format. Let’s break it down:
Assuming the birthdate is in cell A1 (e.g., 31/12/2000), we’ll start with the formula step-by-step:
Age in Years:
=DATEDIF(A1,TODAY(),"Y")
Months (the number of whole months after subtracting whole years):
=DATEDIF(A1,TODAY(),"YM")
Days (the number of days after subtracting whole months):
=DATEDIF(A1,TODAY(),"MD")
Let’s combine these three formulas and add the necessary texts such as “years,” “months,” and “days”:
=DATEDIF(A1,TODAY(),"Y")&" Years "&DATEDIF(A1,TODAY(),"YM")&" months "&DATEDIF(A1,TODAY(),"MD")&" days"
Array Formula:
=ArrayFormula(IFERROR(IF(DATEVALUE(A1:A5),DATEDIF(A1:A5,TODAY(),"Y")&" Years "&DATEDIF(A1:A5,TODAY(),"YM")&" months "&DATEDIF(A1:A5,TODAY(),"MD")&" days")))
User-Defined Function
For quick and easy age calculations, you can import our custom AGE_CALC function. Here’s how to use it:
When there are no blank cells in the date range:
=AGE_CALC(A1:A5,TODAY())
When there are blank cells in the date range:
=ArrayFormula(IFERROR(IF(DATEVALUE(A1:A5),AGE_CALC(A1:A5,TODAY()))))
I hope this article has helped you understand how to calculate age in Google Sheets. Give it a try and enjoy!
