To calculate someone’s exact age in Excel, use =DATEDIF(A2,TODAY(),"Y") for years and =DATEDIF(A2,TODAY(),"YM") for months, but don’t use the “MD” unit for the leftover days. Microsoft’s own documentation warns that “MD” “may result in a negative number, a zero, or an inaccurate result”. A safer formula for the days is =TODAY()-EDATE(A2,DATEDIF(A2,TODAY(),"M")).
Below: what each DATEDIF unit does, why “MD” goes wrong, a complete formula that gives years, months and days, and how to check your answer.
What DATEDIF Does
DATEDIF (“date difference”) counts the time between a start date and an end date in the unit you choose. It’s an old compatibility function that Excel still supports but doesn’t list in its function wizard, so you have to type it yourself. Google Sheets supports it too.
| Unit | What Microsoft says it returns | Safe to use? |
|---|---|---|
| “Y” | The number of complete years in the period | Yes |
| “M” | The number of complete months in the period | Yes |
| “D” | The number of days in the period | Yes |
| “YM” | The difference between the months, ignoring days and years | Yes |
| “YD” | The difference between the days, ignoring years | Yes |
| “MD” | The difference between the days, ignoring months and years | No: documented known issue |
If the start date is later than the end date, DATEDIF returns a #NUM! error, so the birth date always goes first.
Why the “MD” Unit Is a Problem
“MD” is meant to give the days left over after the last complete month, which is exactly the third number you need for an age like “36 years, 6 months, 10 days”. But months have different lengths, and “MD” works by ignoring the months entirely. Around month ends, especially when the birthday falls on the 29th, 30th or 31st, that shortcut can go wrong. Microsoft’s DATEDIF support page lists it under “Known issues”, and users regularly report odd results in Microsoft’s community forums.
Microsoft’s suggested workaround subtracts the first day of the end month from the end date. That only gives the right answer when the start date is also the 1st of a month, so it doesn’t help for birthdays.
A Formula That Works: Years, Months and Days
With the date of birth in cell A2, use these three formulas:
- Years:
=DATEDIF(A2,TODAY(),"Y") - Months:
=DATEDIF(A2,TODAY(),"YM") - Days:
=TODAY()-EDATE(A2,DATEDIF(A2,TODAY(),"M"))
The days formula works in two steps. DATEDIF(A2,TODAY(),"M") counts the complete months since birth. EDATE then adds that many months to the birth date, which gives the most recent “month-iversary”, and subtracting it from today leaves the extra days. Because EDATE handles month lengths properly, the result is never negative.
To show everything in one cell:
=DATEDIF(A2,TODAY(),"Y")&" years, "&DATEDIF(A2,TODAY(),"YM")&" months, "&TODAY()-EDATE(A2,DATEDIF(A2,TODAY(),"M"))&" days"
To work out the age on a particular date instead of today, put that date in B2 and replace every TODAY() with B2.
Worked Example
Someone born on 15 March 1990, measured on 25 September 2026:
- Complete years: 36 (the 36th birthday was 15 March 2026).
- Complete months since then: 6 (15 March to 15 September 2026).
- Days since 15 September: 10.
So the age is 36 years, 6 months and 10 days. “M” returns 438 complete months, EDATE(15 March 1990, 438) is 15 September 2026, and 25 September minus 15 September is 10 days. The free age calculator gives the same answer, which makes it a quick way to check a spreadsheet.
Birthdays on the 29th, 30th or 31st
When a birthday falls on a day that some months don’t have, there’s no single “right” answer, only conventions. EDATE counts one month after 31 January as the last day of February, so someone born on 31 January is 1 month and 1 day old on 1 March in a non-leap year. Our age calculator uses the same convention. Whatever method you use, apply it consistently across a spreadsheet, and check a few month-end birthdays by hand.
Common Mistakes
- Dates stored as text. If a date is left-aligned in the cell or was pasted from another system, Excel may treat it as text, and DATEDIF returns
#VALUE!. Convert it with=DATEVALUE(A2)or re-enter it as a date. - Dates in the wrong order. The birth date must be the first argument, or you get
#NUM!. - Using YEAR(TODAY())-YEAR(A2). This ignores whether the birthday has happened yet this year, so it’s a year too high for part of every year.
- Dividing days by 365.
=(TODAY()-A2)/365drifts because of leap years. Use DATEDIF “Y” for whole years.
Frequently Asked Questions
What is the formula to calculate age in Excel?
Use =DATEDIF(A2,TODAY(),”Y”) with the date of birth in A2. It returns the number of complete years, which is the age most people mean. Add “YM” for months, and use TODAY()-EDATE(A2,DATEDIF(A2,TODAY(),”M”)) for the remaining days.
Why does DATEDIF give a negative number of days?
That’s the “MD” unit’s known issue. Microsoft documents that it can return a negative number, a zero or an inaccurate result. Replace it with TODAY()-EDATE(A2,DATEDIF(A2,TODAY(),”M”)), which counts days from the last complete month.
Does DATEDIF work in Google Sheets?
Yes. Google Sheets supports DATEDIF with the same units, so the formulas above work there too. Test them on a few known dates before relying on them for a whole list.
Why can’t I find DATEDIF in Excel’s function list?
It’s a legacy compatibility function that Excel keeps for older workbooks but doesn’t show in autocomplete or the Insert Function dialog. Type it in full and it works normally.
Need an exact age without a spreadsheet? Enter a date of birth in the free age calculator for years, months and days, or see total days lived with how many days old am I.