Age Calculator in Google Sheets is a practical way to calculate a person’s completed age from a date of birth, especially when you need to calculate ages for a whole column of people. Google Sheets supports the DATEDIF function for calculating the number of whole years, months, or days between two dates, and TODAY() for the current date. Google’s DATEDIF documentation
The simplest formula is =DATEDIF(A2,TODAY(),"Y") when the date of birth is in A2. Google documents the Y unit as the number of whole years between the start and end dates. Google Sheets DATEDIF function
This guide shows how to build an age calculator in Google Sheets for current age, age on a fixed date, years-months-days, total days, total months, weeks, and timestamp-based calculations. It also covers blank dates, copying formulas down a list, fixed reference dates, leap-year testing, and common DATEDIF mistakes.
Quick Answer: Google Sheets Age Formula
If the date of birth is stored in A2 and you want completed age as of today, enter:
=DATEDIF(A2,TODAY(),"Y")The result is the number of complete years between the date in A2 and the date returned by TODAY(). Google says TODAY returns the current date and has no time component. Google Sheets TODAY function
For a fixed reference date in B2, use:
=DATEDIF(A2,B2,"Y")A fixed date is preferable when the age must remain tied to an application cutoff, event date, reporting date, or historical date rather than changing every time the sheet recalculates.
How to Set Up an Age Calculator in Google Sheets
A simple worksheet can use one column for dates of birth, another for the reference date, and another for the calculated age.
| Column | Purpose | Example |
|---|---|---|
| A | Date of birth | 15-Jun-2000 |
| B | Reference date | 21-Sep-2026 |
| C | Completed age | 26 |
| D | Years and months | 26 years, 3 months |
| E | Total days | 9,589 |
Enter real date values rather than text that only looks like a date. Google Sheets date functions expect a date value, a reference to a cell containing a date, or a function that returns a date. The DATE function can also construct a date from numeric year, month, and day values. Google Sheets DATEDIF documentation Google Sheets DATE documentation
1. Calculate Current Age from Date of Birth
For a continuously updating age, use TODAY() as the end date:
=DATEDIF(A2,TODAY(),"Y")If A2 contains 15-Jun-2000, the formula calculates completed years as of the current date. Because TODAY is a volatile function, its value can change when the spreadsheet recalculates. Google documents TODAY as returning the current date without a time component. Google Sheets TODAY function
Leave the result blank when no DOB is entered
For a list where some rows may not have a birth date yet, wrap the calculation in IF:
=IF(A2="","",DATEDIF(A2,TODAY(),"Y"))This prevents the formula from trying to calculate an age before a date has been supplied. Google Sheets community guidance also uses an IF check around DATEDIF for missing DOB values. Google Sheets community example
2. Calculate Age on a Specific Date
For an age that must be calculated against a particular date, place the reference date in B2:
=DATEDIF(A2,B2,"Y")This is useful for school records, event attendance, eligibility lists, historical reports, and any worksheet where the age should not change tomorrow. If you need one common cutoff date for many rows, place it in a single cell such as B1 and use an absolute reference:
=DATEDIF(A2,$B$1,"Y")The dollar signs keep B1 fixed when the formula is copied down. A Google Sheets community answer specifically recommends replacing TODAY() with a fixed cell when the age needs to remain based on the date of an activity or event. Google Sheets community example
3. Calculate Exact Age in Years and Months
Google Sheets supports the YM DATEDIF unit for whole months remaining after complete years. Google Sheets DATEDIF function
=DATEDIF(A2,B2,"Y")&" years, "&DATEDIF(A2,B2,"YM")&" months"For a current result, replace B2 with TODAY():
=DATEDIF(A2,TODAY(),"Y")&" years, "&DATEDIF(A2,TODAY(),"YM")&" months"This format is useful when a plain number such as 26 does not provide enough detail. It reports completed years first and the remaining completed months after those years.
4. Calculate Age in Years, Months and Days
Google Sheets documents MD as the number of days between the start and end dates after subtracting whole months, and YM as whole months after subtracting whole years. Google Sheets DATEDIF function
=DATEDIF(A2,B2,"Y")&" years, "&DATEDIF(A2,B2,"YM")&" months, "&DATEDIF(A2,B2,"MD")&" days"For ordinary age calculations this can be a convenient compact formula. However, test your exact date range if the worksheet will be used for formal records, especially around month-end and leap-day cases. The Google documentation notes that DATEDIF’s month and year counting depends on whether the dates reach or pass the relevant day of the month.
5. Calculate Total Days of Age
If you need elapsed days rather than completed years, use the D unit:
=DATEDIF(A2,B2,"D")You can also subtract dates directly:
=B2-A2Google Sheets represents dates as numeric values, so date subtraction can return the number of days between the two dates. If the result is displayed as a date instead of a number, change the cell’s number format to Number. Google documents this behavior in its date-function guidance. Google Sheets DATEDIF function
6. Calculate Total Months of Age
For complete months between two dates, use the M unit:
=DATEDIF(A2,B2,"M")For completed months as of today:
=DATEDIF(A2,TODAY(),"M")Google defines M as the number of whole months between the start and end dates. Google Sheets DATEDIF function
7. Calculate Age in Weeks
Google Sheets does not need a separate age-in-weeks function. Calculate elapsed days and divide by seven:
=(B2-A2)/7For completed whole weeks, use INT:
=INT((B2-A2)/7)For today’s completed weeks:
=INT((TODAY()-A2)/7)These are elapsed-week calculations. They should not be confused with calendar-week numbering, which answers a different question.
8. Calculate Age in Hours, Minutes and Seconds
When the birth value and reference value include time, the difference can be converted to smaller units.
| Result | Formula | Conversion |
|---|---|---|
| Days | =B2-A2 | 1 day |
| Hours | =(B2-A2)*24 | 24 hours per day |
| Minutes | =(B2-A2)*1440 | 1,440 minutes per day |
| Seconds | =(B2-A2)*86400 | 86,400 seconds per day |
For dates without a time component, the calculation starts and ends at the corresponding midnight values. For timestamp-based calculations, make sure both cells contain the intended date and time.
9. Copy the Age Formula Down an Entire List
One of the main advantages of Google Sheets is that you can use the same formula for many people. If dates of birth are in A2:A, enter the formula in B2 and fill it down:
=IF(A2="","",DATEDIF(A2,TODAY(),"Y"))Each row then uses its own date of birth. If you prefer one formula that automatically handles a larger column, you can use an array approach, but a simple filled-down formula is often easier to audit and maintain.
10. Use a Fixed Cutoff Date for Every Person
For an eligibility list, put the cutoff date in B1 and use:
=IF(A2="","",DATEDIF(A2,$B$1,"Y"))Now the same reference date is applied to every person. This is safer than TODAY() when the age must reproduce the result of a specific event date.
| Scenario | Reference date | Formula |
|---|---|---|
| Current age | Today | =DATEDIF(A2,TODAY(),”Y”) |
| Historical age | Fixed date in B2 | =DATEDIF(A2,B2,”Y”) |
| Common cutoff | Fixed date in B1 | =DATEDIF(A2,$B$1,”Y”) |
| Total days | Fixed date in B2 | =DATEDIF(A2,B2,”D”) |
| Total months | Fixed date in B2 | =DATEDIF(A2,B2,”M”) |
Google Sheets Age Calculator with a Blank-Safe Formula
A practical worksheet should account for rows where a date of birth has not been entered. The blank-safe formula is:
=IF(A2="","",DATEDIF(A2,TODAY(),"Y"))If you also want to catch invalid or unexpected date values without showing an error, you can use IFERROR:
=IFERROR(DATEDIF(A2,TODAY(),"Y"),"")Use IFERROR deliberately rather than hiding every problem automatically. During worksheet development, visible errors can make incorrect inputs easier to find. Once the data-entry process is stable, a blank-safe display may be more useful for end users.
Google Sheets DATEDIF Units Explained
| Unit | Meaning | Typical use |
|---|---|---|
| Y | Whole years | Completed age |
| M | Whole months | Total completed months |
| D | Days | Total elapsed days |
| YM | Whole months after whole years | Years + remaining months |
| MD | Days after whole months | Remaining days |
| YD | Days assuming dates are no more than one year apart | Days since last birthday |
These meanings come directly from Google’s DATEDIF documentation. Google also notes that months and years are counted only when the relevant day is reached or passed, which is important around month-end. Google Sheets DATEDIF documentation
Common Google Sheets Age-Calculation Mistakes
- Using YEAR(TODAY())-YEAR(A2) when you need completed age rather than a simple calendar-year difference.
- Using TODAY() for a calculation that actually needs a historical cutoff date.
- Forgetting to make a shared reference date absolute with $ signs.
- Trying to calculate an age when the DOB cell is blank.
- Storing a date as text that Google Sheets cannot interpret as the intended date.
- Formatting a numeric DATEDIF result as a date, which can make a correct day count look like a calendar date.
- Ignoring the effect of month-end dates when using month and year units.
- Testing only ordinary dates and not checking birthdays, leap days, and boundary dates.
Why TODAY() Can Change Your Age Result
TODAY() is designed to return the current date. Google states that it represents the current date when the spreadsheet was last recalculated, rather than remaining at the date when the formula was first entered. Google Sheets TODAY function
That is exactly what you want for a live age calculator. It is not what you want when calculating age at a past event. For an event-based calculation, store the event date in a cell and use that cell as the DATEDIF end date.
DATEDIF Errors and Date Formatting
If DATEDIF gives an unexpected result, first check that the start date and end date are valid date values and that the start date is not later than the end date. Also check the cell’s number format. Google’s documentation notes that a DATEDIF day result can appear like a calendar date if a Date format has been applied; changing the format to Number displays the expected numeric result. Google Sheets DATEDIF function
Example: A Complete Google Sheets Age Table
Suppose the sheet contains a birth date in A2 and a fixed reference date in B2. The following formulas can be used side by side:
| Output | Formula |
|---|---|
| Completed years | =DATEDIF(A2,B2,”Y”) |
| Years and months | =DATEDIF(A2,B2,”Y”)&” years, “&DATEDIF(A2,B2,”YM”)&” months” |
| Years, months, days | =DATEDIF(A2,B2,”Y”)&” years, “&DATEDIF(A2,B2,”YM”)&” months, “&DATEDIF(A2,B2,”MD”)&” days” |
| Total months | =DATEDIF(A2,B2,”M”) |
| Total days | =DATEDIF(A2,B2,”D”) |
| Total weeks | =INT((B2-A2)/7) |
Keeping the birth date and reference date in separate cells makes the worksheet easier to audit. It also lets you change the reference date without rewriting every formula.
How to Test Your Google Sheets Age Calculator
- Test a person whose birthday is exactly the reference date.
- Test a person whose birthday is tomorrow.
- Test a person whose birthday was yesterday.
- Test a date near the end of a month.
- Test a February 29 birth date against a non-leap-year reference date.
- Test a blank DOB row.
- Test a reference date earlier than the DOB and decide how the sheet should display the error.
- Change a fixed reference date and confirm every row updates consistently.
Boundary testing matters when the sheet is used for eligibility, attendance, enrollment, or other decisions. The formula calculates the date interval; your organization’s rule determines what that interval means for the particular use case.
Google Sheets vs Excel for Age Calculation
The core DATEDIF approach is very similar in Google Sheets and Excel, so a worksheet built around date-of-birth and reference-date cells can usually be adapted between the two spreadsheet applications. The exact function behavior and documentation should still be checked in the application being used.
| Need | Google Sheets | Excel |
|---|---|---|
| Current age from DOB | DATEDIF + TODAY | DATEDIF + TODAY |
| Age on fixed date | DATEDIF + cell | DATEDIF + cell |
| Whole months | DATEDIF M | DATEDIF M |
| Years + months | DATEDIF Y + YM | DATEDIF Y + YM |
| Collaborative cloud sheet | Native strength | Depends on Microsoft 365 setup |
| Offline desktop workbook | Limited compared with desktop apps | Native strength |
If you are working in Microsoft Excel rather than Google Sheets, see the site’s Age Calculator in Excel guide for Excel-specific formulas and worksheet setup.
Google Sheets Age Calculator FAQ
What is the formula for age in Google Sheets?
Use =DATEDIF(A2,TODAY(),"Y") when A2 contains the date of birth and you want completed age as of today. Google documents Y as whole years between the two dates. Google Sheets DATEDIF function
How do I calculate age on a specific date in Google Sheets?
Put the date of birth in A2 and the reference date in B2, then use =DATEDIF(A2,B2,"Y").
How do I calculate age without letting it change every day?
Use a fixed reference date in a cell instead of TODAY(). For example, =DATEDIF(A2,$B$1,"Y") uses the same cutoff date for every row.
How do I calculate age in years, months and days in Google Sheets?
Use the Y, YM, and MD DATEDIF units together, such as =DATEDIF(A2,B2,"Y")&" years, "&DATEDIF(A2,B2,"YM")&" months, "&DATEDIF(A2,B2,"MD")&" days". Google documents all three units. Google Sheets DATEDIF function
Why is my DATEDIF result showing as a date?
The result cell may have a Date format. Google specifically notes that a DATEDIF day result can display like a date when the cell has a date format. Change the number format to Number. Google Sheets DATEDIF function
Can Google Sheets calculate total months of age?
Yes. Use =DATEDIF(A2,B2,"M") for complete months between the two dates.
Can Google Sheets calculate age in days?
Yes. Use =DATEDIF(A2,B2,"D") or subtract the dates directly with =B2-A2.
Does TODAY() include the current time?
No. Google documents TODAY as returning the current date without a time component. NOW() returns both date and time. Google Sheets TODAY function
Sources and Further Reading
- Google Docs Editors Help — DATEDIF.
- Google Docs Editors Help — TODAY.
- Google Docs Editors Help — NOW.
- Google Docs Editors Help — DATE.
- Google Docs Editors Community — age calculation example.
Conclusion
A reliable age calculator in Google Sheets can be built with a few date formulas. For a live current-age column, =DATEDIF(A2,TODAY(),"Y") is the simplest starting point. For a historical or eligibility calculation, use a fixed reference date instead. Add IF or IFERROR when you need blank-safe handling, and test boundary dates before using the sheet for important records.
The key idea is simple: the date of birth supplies the starting point, the reference date supplies the end point, and DATEDIF determines the completed interval. Once those two dates are controlled correctly, the same sheet can report age in years, months, days, weeks, or smaller time units.
