{"id":223,"date":"2026-10-01T02:02:44","date_gmt":"2026-10-01T02:02:44","guid":{"rendered":"https:\/\/thenesttools.com\/blog\/?p=223"},"modified":"2026-10-01T02:27:59","modified_gmt":"2026-10-01T02:27:59","slug":"excel-datedif-wrong-age","status":"publish","type":"post","link":"https:\/\/thenesttools.com\/blog\/excel-datedif-wrong-age\/","title":{"rendered":"Why Excel’s DATEDIF Gives the Wrong Age (and a Formula That Works)"},"content":{"rendered":"

To calculate someone’s exact age in Excel, use =DATEDIF(A2,TODAY(),\"Y\")<\/code> for years and =DATEDIF(A2,TODAY(),\"YM\")<\/code> for months, but don’t use the “MD” unit for the leftover days.<\/strong> 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\"))<\/code>.<\/p>\n

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.<\/p>\n

What DATEDIF Does<\/h2>\n

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.<\/p>\n\n\n\n\n\n\n\n\n\n\n
Unit<\/th>\nWhat Microsoft says it returns<\/th>\nSafe to use?<\/th>\n<\/tr>\n<\/thead>\n
“Y”<\/td>\nThe number of complete years in the period<\/td>\nYes<\/td>\n<\/tr>\n
“M”<\/td>\nThe number of complete months in the period<\/td>\nYes<\/td>\n<\/tr>\n
“D”<\/td>\nThe number of days in the period<\/td>\nYes<\/td>\n<\/tr>\n
“YM”<\/td>\nThe difference between the months, ignoring days and years<\/td>\nYes<\/td>\n<\/tr>\n
“YD”<\/td>\nThe difference between the days, ignoring years<\/td>\nYes<\/td>\n<\/tr>\n
“MD”<\/td>\nThe difference between the days, ignoring months and years<\/td>\nNo: documented known issue<\/strong><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

If the start date is later than the end date, DATEDIF returns a #NUM!<\/code> error, so the birth date always goes first.<\/p>\n

Why the “MD” Unit Is a Problem<\/h2>\n

“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<\/a> lists it under “Known issues”, and users regularly report odd results in Microsoft’s community forums<\/a>.<\/p>\n

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.<\/p>\n

A Formula That Works: Years, Months and Days<\/h2>\n

With the date of birth in cell A2, use these three formulas:<\/p>\n