How to Calculate Age in Excel From a Date of Birth
To calculate age in Excel, put the date of birth in one cell and use the formula =DATEDIF(B2,TODAY(),”Y”) in another. That returns the number of full years between the birth date and today, which is the age most people mean. The sections below show other ways to calculate age in Excel, covering months, days, a fixed date and Google Sheets.
- Age in whole years
=DATEDIF(B2,TODAY(),"Y") - Leftover months
=DATEDIF(B2,TODAY(),"YM") - Leftover days
=DATEDIF(B2,TODAY(),"MD") - Full sentence
=DATEDIF(B2,TODAY(),"Y")&" years, "&DATEDIF(B2,TODAY(),"YM")&" months, "&DATEDIF(B2,TODAY(),"MD")&" days" - Decimal age
=YEARFRAC(B2,TODAY(),1) - Total days
=TODAY()-B2
Spreadsheets are the usual place for ages in HR records, school rosters, membership lists and clinic logs. Typing ages by hand creates errors that grow every year, while a formula updates itself each time the file opens. Once the formula is set, you can copy it down a whole column in seconds.
The DATEDIF Formula for Age in Years
DATEDIF takes three arguments: a start date, an end date and a unit code in quotation marks. With the birth date in B2, =DATEDIF(B2,TODAY(),"Y") counts complete years and ignores any partial year. Someone born on October 20, 2000 will show 25 on October 19, 2026 and 26 the next day.
Excel does not list DATEDIF in its function suggestions, because Microsoft keeps it mainly for compatibility with older spreadsheets. It still works in every current version of Excel on Windows and Mac. Type it in full, and Excel will accept it even without the usual tooltip.
| Unit code | Returns |
|---|---|
| "Y" | Complete years |
| "M" | Complete months |
| "D" | Total days |
| "YM" | Months left after the years |
| "MD" | Days left after the months |
Age in Years, Months and Days
For a full breakdown, use the unit codes "YM" for leftover months and "MD" for leftover days. The formula =DATEDIF(B2,TODAY(),"Y")&" years, "&DATEDIF(B2,TODAY(),"YM")&" months, "&DATEDIF(B2,TODAY(),"MD")&" days" builds a readable sentence. The ampersands join the three numbers with text labels in between.
Microsoft notes that the "MD" unit can return odd results in some month end cases. If exact day counts matter, such as for an infant's age or a legal deadline, check a few results against our age calculator. For most rosters and reports, the formula is accurate enough to calculate age in Excel without any extra steps.
Build a sentence
- 1YearsDATEDIF with "Y"
- 2MonthsDATEDIF with "YM"
- 3DaysDATEDIF with "MD", then join with ampersands
Using YEARFRAC for Decimal Ages
YEARFRAC returns the fraction of a year between two dates, so =YEARFRAC(B2,TODAY(),1) gives an age such as 34.62. Wrap it in INT, as in =INT(YEARFRAC(B2,TODAY(),1)), to drop the decimal and get whole years. The final argument 1 tells Excel to use the actual number of days in each year, which handles leap years correctly.
Decimal ages are useful for research data, insurance pricing and statistical work where a half year matters. They also make it easy to sort or chart ages precisely. When you simply need to calculate age in Excel for a roster, DATEDIF is usually the cleaner choice.
| =YEARFRAC(B2,TODAY(),1) | 34.62 |
| =INT(YEARFRAC(B2,TODAY(),1)) | 34 |
Basis 1 uses the real number of days in each year.
Calculating Age on a Specific Date
Many tasks need age on a fixed date rather than today, such as an enrollment cutoff or the end of a plan year. Replace TODAY() with a cell reference or with DATE(2026,9,1), so the formula becomes =DATEDIF(B2,DATE(2026,9,1),"Y"). The result then stays the same no matter when the file is opened.
Keeping the reference date in its own cell is even better, since you can change it once and every row updates. Label that cell clearly so others understand the sheet. This approach is the safest way to calculate age in Excel for audits and official reports.
| Today | TODAY() |
| A fixed date | DATE(2026,9,1) |
| A reference cell | $E$1 |
A labeled reference cell lets every row update at once.
Google Sheets and Common Errors
Google Sheets supports the same DATEDIF and YEARFRAC formulas with identical syntax. You can paste any formula from this page into a Sheets cell and get the same result. Sheets even suggests DATEDIF as you type, which Excel does not.
The most common error is a #VALUE! result, which usually means the birth date is stored as text rather than a real date. Retype the date or use the DATEVALUE function to convert it. A #NUM! error means the start date is later than the end date, so check that the columns are not swapped.
| Error | Usual cause | Fix |
|---|---|---|
| #VALUE! | Birth date stored as text | Retype it or use DATEVALUE |
| #NUM! | Start date after end date | Check the columns are not swapped |
Frequently Asked Questions
What is the formula to calculate age in Excel?
Use =DATEDIF(B2,TODAY(),"Y") with the birth date in B2. It returns completed years as of today. Copy it down the column for every row.
Why can't I find DATEDIF in Excel?
Excel keeps DATEDIF as a hidden compatibility function. It does not appear in the suggestions list. Type it in full and it will still work.
How do I calculate age in years and months in Excel?
Combine DATEDIF with the "Y" and "YM" units. Join them with text using ampersands. The result reads like 34 years, 7 months.
How do I calculate age on a specific date in Excel?
Replace TODAY() with a date cell or a DATE function. For example, use DATE(2026,9,1) for September 1, 2026. The age then stays fixed to that date.
Does the age formula work in Google Sheets?
Yes, Google Sheets supports DATEDIF and YEARFRAC. The syntax is identical to Excel. You can paste the same formulas directly.