Are you struggling to calculate age from birthdates in Google Sheets? Look no further! In this guide, we will explore both non-array and array formulas, as well as a user-defined function, to help you accurately calculate age in years, months, and days.
Calculate Age from Birthdates in Years Only in Google Sheets
Let’s start with the non-array formulas. In cell A1, enter the birthdate in the format DD/MM/YYYY. Then, in cell B1, use either of the following formulas:
=int(yearfrac(A1,today(),1))
=datedif(A1,today(),"Y")The first formula uses the YEARFRAC function, and the number 1 represents the day count conversion. For more details, check out the comprehensive date functions guide on our website.
Now, if you want to calculate the age for a group of people, such as students or team members, we can use an array formula. Simply enter the following formula in an adjacent column:
=ArrayFormula(iferror(if(datevalue(A3:A7),int(yearfrac(A3:A7,today(),1)))))Alternatively, you can use the DATEDIF function in the array formula:
=ArrayFormula(iferror(if(datevalue(A3:A7),datedif(A3:A7,today(),"Y"))))To determine whether a cell contains a date, we utilize the DATEVALUE function. This allows us to calculate the age using DATEDIF or YEARFRAC, should the cell contain a valid date. To handle arrays, we must employ the ArrayFormula function.
Calculate Age from Birthdate in Year, Month, and Day in Google Sheets
Here, we present two methods: a formula approach and a custom function.
Formula Approach
To calculate age in year, month, and day format, we recommend using the versatile DATEDIF function. Let’s break it down step-by-step using an example birthdate of 31/12/2000:
- 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")
Combine these three formulas and add the necessary text to display years, months, and days:
=datedif(A1,today(),"Y")&" Years "&datedif(A1,today(),"YM")&" months "&datedif(A1,today(),"MD")&" days"
For arrays, use the following 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 a quick and efficient solution, you can import our custom AGE_CALC function. Follow these steps:
- Copy the code for the
AGE_CALCfunction from our website. - Paste it into your Google Sheets script editor.
- Use the function as shown below:
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()))))
And voila! You now have the perfect tool to calculate age in years, months, and days. We hope you find this guide helpful. If you have any further questions, feel free to reach out. Enjoy calculating age in Google Sheets like a pro!
Check out Crawlan.com for more expert Google Sheets tips and tricks.
Disclaimer: The information provided in this article is to be used for educational purposes only. Always double-check formulas and calculate ages accurately.
