Learn how to calculate age or duration in Google Sheets using the powerful AGE_CALC Named Function. This function returns results in the format “x years, x months, x days” and works for single dates or ranges of dates.
Why Use AGE_CALC in Google Sheets?
- ✅ Returns age or duration in years, months, and days.
- ✅ Works as an array function for multiple dates at once.
- ✅ More customizable and user-friendly than formula-only solutions.
- ✅ Ideal for tracking birthdays, task durations, or project timelines.
Before we dive into examples, the first thing you need to do is import the AGE_CALC Named Function into your Google Sheet. Once imported, you’ll be able to use it in formulas to calculate age or duration effortlessly.
Step 1: Import the AGE_CALC Named Function
- Make a copy of the sample sheet.
- You can use the function in that sheet. To use it on another sheet:
- Open that sheet and go to Data > Named Function.
- Click Import Function in the sidebar.
- Follow the on-screen instructions.
Tip: Importing the function first ensures that formulas work without errors.
AGE_CALC Syntax and Arguments
Syntax:
AGE_CALC(dob, end_date)Arguments:
| Argument | Description |
|---|---|
| dob | The cell or array containing the date of birth or start date. |
| end_date | The cell or array containing the end date (today or any specific date). |
Tip: Use TODAY() for dynamic calculations based on the current date.
Step 2: Calculate Age or Duration for a Single Date
Example #1 – Age Based on Today’s Date
=AGE_CALC(DATE(2002, 5, 10), TODAY())Returns: “20 years, 6 months, 4 days.”
Note: When this tutorial was written, TODAY() was 2022-11-14, so the output corresponds to that date.
Example #2 – Age Based on a Specific Date
=AGE_CALC(DATE(2002, 5, 10), DATE(2022, 11, 14)) // returns “20 years, 6 months, 4 days.”Note: The result is calculated as per the end date provided (2022-11-14).
Step 3: Calculate Age or Duration for Multiple Dates (Array Formula)
Example #1 – Calculate Duration Between Two Dates
If you have start dates in A2:A and end dates in B2:B, use:
=ARRAYFORMULA(IF(A2:A="",,AGE_CALC(A2:A, B2:B)))Note: This calculates duration, not age.
Example #2 – Calculate Age from Birth Dates
If you only have birth dates in A2:A and want the age as of today while skipping blank rows:
=ARRAYFORMULA(IF(A2:A="",,AGE_CALC(A2:A, TODAY())))Note: This calculates age.
Step 4: Split Age Into Separate Columns (Years, Months, Days)
To separate AGE_CALC output into three columns:
=ARRAYFORMULA(IFERROR(TRIM(SPLIT(B2:B, ","))))
FAQ – AGE_CALC Named Function
Q1: Can I calculate age for future dates?
A1: Yes, AGE_CALC works for any start and end date, including future dates.
Q2: What if the cell is blank?
A2: Use ARRAYFORMULA(IF(A2:A="",,AGE_CALC(A2:A, TODAY()))) to skip blank rows.
Q3: Can I split results into years, months, and days?
A3: Yes, use SPLIT() combined with ARRAYFORMULA as shown above.
Q4: How is AGE_CALC different from formulas like DATEDIF?
A4: AGE_CALC is easier to read, works as an array, and automatically formats the output as “x years, x months, x days.”
Final Thoughts
The AGE_CALC Named Function simplifies age and duration calculations in Google Sheets. Whether for single dates, ranges, or dynamic calculations based on today’s date, it’s a versatile and user-friendly solution.
You May Also Like: How to Create Named Functions in Google Sheets
