The purpose of the AGE_CALC Named Function is to return age in years, months, and days from a birth date to another date in Google Sheets.
The output will be like x years, x months, x days. If you want, you can split them into separate cells.
Moreover, AGE_CALC is an array function. So, you can convert a set of dates of birth to age in a flash.
The function takes two arguments, a starting date (date of birth) and an end date (today’s date or some other date).
So you can use the same function to get task duration in the above format.
We have a formula-based solution to get the same output – Google Sheets: Formula to Calculate Age from Birthday. But this function is easy to use and also more customized.
For example, when the formula returns “0 Years 5 months & 6 days,” the function returns “5 months, 6 days.”
Syntax and Arguments of the AGE_CALC Named Function in Google Sheets
Syntax:
AGE_CALC(dob, end_date)Arguments:
dob – The cell or array contains the date of birth/start date.
end_date – The cell or array contains the date representing when the observation’s age is of interest.
Here, you can hardcode today() within the formula if you want to return the age based on today’s date.
Now, let’s see how to calculate age from birth date using the AGE_CALC Named Function in Google Sheets.
Examples to Calculate Age From Birth Date Using the AGE_CALC Function
Non-Array Formula
Example # 1: Age Based on Today’s Date.
=AGE_CALC(date(2002,5,10),today())In the above example, the dob is 10th June 2002, and the end_date is today’s date.
You can also use =AGE_CALC("10/05/2002",today()). But, the dob must be as per the date format in your Sheets.
Example # 2: Age Based on a Particular Date.
=AGE_CALC(date(2002,5,10),date(2022,11,14))Here the AGE_CALC Named Function will return the age 20 years, 6 months, 4 days. The alternative formula is =AGE_CALC("10/05/2002","14/11/2022").
In all the above formulas, you can use cell references. Please see the examples below.
AGE_CALC Array Formula to Calculate Age or Duration
When you want to calculate the age from a set of dates of birth, you can use the AGE_CALC Named Function in the following way.
Assume the dob is in A2:A and the end_date is in B2:B.
Insert the following AGE_CALC array formula in C2.
=AGE_CALC(A2:A,B2:B)If you have only the dob in A2:A and want to use today() in end_date, you must additionally use an IF logical test and Array Formula to skip blank rows in the range.
=ArrayFormula(if(A2:A="",,AGE_CALC(A2:A,today())))
Needless to say, to calculate duration in x years, x months, and x days format, you just input the start date in dob and end date in end_date.
What about splitting the result into three columns, one each for years, months, and days?
If you follow my last example in cell B2 (see the image above), use the following SPLIT in cell C2.
=ArrayFormula(iferror(trim(split(B2:B,","))))Importing AGE_CALC Function
To use the above custom Named Function, you must first import it to your Sheet.
To import, make a copy of my sample Sheet below.
Then select Data > Named Function from within your Sheet. On the sidebar panel, select “Import function.”
Follow the onscreen instructions, and voila!
If you have any queries regarding using my AGE_CALC Named Function and calculating age from the birth date, please feel free to ask me in the comments below.
You May Like: How to Create Named Functions in Google Sheets.
