Whether you are managing a mailing list, tracking employee milestones, or organizing a student database, knowing how to calculate age in Google Sheets is a fundamental skill. While there isn't a single "AGE" button, Google Sheets provides a powerful hidden function called DATEDIF that makes this process incredibly simple.
Interactive Age Preview
Before we dive into the formulas, try this tool to see how logic handles date differences.
The Secret Weapon: The DATEDIF Function
The most efficient way to calculate age in Google Sheets is using the DATEDIF function. Interestingly, this function doesn't appear in the standard autocomplete list in Sheets, but it works perfectly.
The Syntax:
- start_date: The date of birth (e.g., cell A2).
- end_date: The date you want to calculate age against (usually
TODAY()). - unit: The type of information you want (e.g., "Y" for years).
Step-by-Step Examples
1. Calculate Age in Full Years
If you just need the number of years someone has been alive, use the "Y" unit. If the date of birth is in cell A2, use this formula:
2. Calculate Age in Years, Months, and Days
Sometimes you need more precision. To get a result like "25 Years, 4 Months, 12 Days", you need to combine several DATEDIF functions using the ampersand (&) symbol:
DATEDIF(A2, TODAY(), "YM") & " Months, " &
DATEDIF(A2, TODAY(), "MD") & " Days"
In this formula:
- "Y" gives the total years.
- "YM" gives the months remaining after the years are subtracted.
- "MD" gives the days remaining after the months are subtracted.
Using YEARFRAC for Decimal Age
If you are performing statistical analysis and need the age as a decimal (e.g., 25.42 years), the YEARFRAC function is your best friend.
This calculates the fraction of a year between two dates based on a specific day-count convention.
Pro Tip: Handling Empty Cells
If you drag your formula down a column where some rows are empty, you might see an error or a weird number (like 124 years). To prevent this, wrap your formula in an IF statement:
This tells Google Sheets: "If cell A2 is blank, show nothing; otherwise, calculate the age."
Conclusion
Mastering DATEDIF is a small but mighty productivity win. It ensures your data stays dynamic—as time passes and TODAY() updates, your sheet will automatically reflect the correct ages without you ever having to lift a finger again.