Calculate Age from Birthday in Excel and Google Sheets
Purpose
Calculating age sounds like it should be simple — just subtract the birth date from today. The problem is that…
Calculating age sounds like it should be simple — just subtract the birth date from today. The problem is that naive date subtraction gives you the number of days, not years, and then you have to handle leap years, month lengths, and whether the person has had their birthday yet this year. I learned this the hard way when I built an HR dashboard that showed ages half a year out because I divided days by 365.25 without accounting for the current year properly. DATEDIF solves all of this cleanly. It takes a start date, an end date, and a unit code, and returns the completed difference in whatever unit you ask for. Use it with TODAY() and it always gives you the current age in whole years, automatically updating every day the spreadsheet is opened. It is available in both Excel and Google Sheets, though Excel keeps it undocumented for historical reasons. Type it directly and it works perfectly.
Syntax
✓ Excel 2003+
✓ Google Sheets
= Same syntax
=DATEDIF(start_date, end_date, unit)
Arguments
| Argument | Required | Description |
|---|---|---|
| start_date | Required | The earlier of the two dates. For age calculations this is the date of birth. Must be a valid Excel date — either a cell reference containing a date, or a date entered with DATE(year,month,day). |
| end_date | Required | The later of the two dates. For current age use TODAY(). For age at a specific point use DATE(year,month,day). Must be greater than start_date or DATEDIF returns an error. |
| unit | Required | "Y" returns complete years (standard for age). "M" returns complete months. "D" returns complete days. "YM" returns remaining months after stripping complete years. "MD" returns remaining days after stripping complete months. "YD" returns days since last birthday anniversary. |
How it works
DATEDIF counts the number of complete intervals between two dates. The unit argument controls what you are counting — Y for complete years, M for complete months, D for complete days. For age in years you use Y, which means DATEDIF only increments the count on the person's actual birthday. If today is one day before someone's 30th birthday, DATEDIF returns 29, not 30. This is exactly the right behaviour for age calculations. Combining DATEDIF with TODAY() means the formula recalculates every time the spreadsheet is opened, so the age is always current. You can also use this function for other date intervals — how many months a subscription has been running, how many days a ticket has been open, how many complete years of service an employee has completed.
Examples
1
Calculate the current age in completed years for each employee from their date of birth.
| A | B | C | |
|---|---|---|---|
| 1 | Employee | Date of Birth | Age (Years) |
| 2 | Alice Chen | 1990-03-15 | =DATEDIF(B2,TODAY(),"Y") |
| 3 | Bob Smith | 1985-07-22 | 38 |
| 4 | Carol Jones | 2000-11-08 | 23 |
| 5 | David Kim | 1978-01-30 | 46 |
| 6 | Eve Taylor | 1995-04-12 | 29 |
| 7 | Frank Brown | 1988-09-03 | 35 |
| 8 | Grace Lee | 2001-06-17 | 22 |
| 9 | Hank Patel | 1972-12-25 | 51 |
| 10 | Iris Wong | 1998-02-28 | 26 |
| 11 | James Miller | 1983-08-11 | 40 |
Row 2: 34
— Alice was born on 15 March 1990. The formula counts complete years from that date to today.
The result changes automatically every day the spreadsheet opens. If you open it the day after someone's birthday the age updates. The "Y" unit ensures only complete years count — days do not round up.
2
Show each employee's age in a combined years and months format for a staff directory.
| A | B | C | |
|---|---|---|---|
| 1 | Employee | Date of Birth | Age |
| 2 | Alice Chen | 1990-03-15 | =DATEDIF(B2,TODAY(),"Y")&" yr "&DATEDIF(B2,TODAY(),"YM")&" mo" |
| 3 | Bob Smith | 1985-07-22 | 38 yr 8 mo |
| 4 | Carol Jones | 2000-11-08 | 23 yr 4 mo |
| 5 | David Kim | 1978-01-30 | 46 yr 2 mo |
| 6 | Eve Taylor | 1995-04-12 | 28 yr 11 mo |
| 7 | Frank Brown | 1988-09-03 | 35 yr 7 mo |
| 8 | Grace Lee | 2001-06-17 | 22 yr 9 mo |
| 9 | Hank Patel | 1972-12-25 | 51 yr 3 mo |
| 10 | Iris Wong | 1998-02-28 | 26 yr 1 mo |
| 11 | James Miller | 1983-08-11 | 40 yr 7 mo |
Row 2: 34 yr 0 mo
— Two DATEDIF calls joined with & — the first returns years, the second returns months remaining after stripping complete years.
The YM unit in the second DATEDIF resets to zero each birthday and counts up until the next one. So someone who just turned 34 yesterday shows 34 yr 0 mo.
Common use cases
1. Check whether customers are 18 or over for age-restricted purchases
=IF(DATEDIF(B2,TODAY(),"Y")>=18,"Eligible","Under 18")
2. Calculate years of service for each employee to determine vacation entitlement
=DATEDIF(C2,TODAY(),"Y")
3. Show age in years and months for a patient database
=DATEDIF(B2,TODAY(),"Y")&" yr "&DATEDIF(B2,TODAY(),"YM")&" mo"
4. Flag members whose subscriptions have been running for more than 12 months
=IF(DATEDIF(D2,TODAY(),"M")>12,"Renewal due","Active")
5. Calculate a child's age as of the first day of the school year for enrollment checks
=DATEDIF(B2,DATE(2024,9,1),"Y")
Common errors
#NUM! error
DATEDIF returns #NUM! when start_date is greater than end_date. This happens if the birth date cell contains a future date or if the end date is before the start date.
Fix: Check that the birth date is in the past. Wrap in IFERROR to return a blank or message: =IFERROR(DATEDIF(B2,TODAY(),"Y"),"Check date").
Wrong age — off by one year
When the person's birthday is later this year but you are calculating age, the formula may seem to return the wrong age depending on the date. DATEDIF with "Y" handles this correctly by not counting the current year unless the birthday has passed.
Fix: Confirm you are using "Y" as the unit. If you divided days by 365 or 365.25 that calculation does not correctly handle birthdays and will give fractional or off-by-one results.
Date stored as text
If the birth date column contains dates formatted as text (left-aligned in the cell), DATEDIF cannot calculate correctly and may return #VALUE! or wrong results.
Fix: Select the date column and check whether values are right-aligned (proper dates) or left-aligned (text). Use DATEVALUE to convert text dates to real dates.
Tips and variations
Use DATEDIF with YM to show months remaining after the birthday
Combine two DATEDIF calls to show a more precise age. The "Y" unit gives complete years and "YM" gives remaining complete months since the last birthday. Joined together they read naturally as 34 years 7 months.
=DATEDIF(B2,TODAY(),"Y")&" years "&DATEDIF(B2,TODAY(),"YM")&" months"
Calculate age as of a specific cut-off date, not today
Replace TODAY() with a specific date to calculate age at a historical point. This is useful for school enrollment where eligibility is determined by age on a specific date like September 1st of the school year.
=DATEDIF(B2,DATE(2024,9,1),"Y")
Format the result to show years as a label
DATEDIF returns a plain number. To display it as 34 years in the cell, concatenate with the word years. Note this converts the result to text so you cannot then sum or average it — keep raw numbers in a helper column if needed.
=DATEDIF(B2,TODAY(),"Y")&" years"
Excel vs Google Sheets
Excel vs Google Sheets
DATEDIF works identically in Excel and Google Sheets with the same syntax and unit codes. The only difference is documentation — Google Sheets lists DATEDIF in its function help and autocompletes it, while Excel treats it as undocumented even though it has worked in every Excel version since Excel 97. Both implementations behave the same way and the formulas are fully interchangeable.
Related reading
Frequently asked questions
The most reliable formula is =DATEDIF(birth_date,TODAY(),"Y"). DATEDIF calculates the difference between two dates in completed units — Y for years, M for months, D for days. It handles the complexity of leap years, months with different day counts, and birthday boundaries correctly. YEARFRAC is an alternative but DATEDIF gives more predictable results for age calculations.
DATEDIF is an undocumented function in Excel — it was included for Lotus 1-2-3 compatibility and never officially documented. It works perfectly but does not appear in the formula helper or autocomplete. Just type it directly and it works. Google Sheets documents and autocompletes it normally.
Combine two DATEDIF calls: =DATEDIF(A1,TODAY(),"Y")&" years "&DATEDIF(A1,TODAY(),"YM")&" months". The Y unit gives complete years, and YM gives the remaining months after stripping out complete years.
Yes — replace TODAY() with any date: =DATEDIF(A1,DATE(2024,12,31),"Y") calculates the age as of 31 December 2024. This is useful for calculating ages at a historical point, for eligibility checks at a specific cut-off date, or for year-end reports.
Use =IF(DATEDIF(A1,TODAY(),"Y")>=18,"Adult","Minor") to return Adult or Minor based on age. For a simple true/false check use =DATEDIF(A1,TODAY(),"Y")>=18 which returns TRUE if the person is 18 or older.