DATEDIF
Calculates the difference between two dates in years, months, or days. An undocumented but useful function.
DATEDIF(start_date, end_date, unit)Arguments
start_dateThe earlier dateend_dateThe later dateunit"Y"=years, "M"=months, "D"=days, "YM"=months excl years, "MD"=days excl months
=DATEDIF(A1,TODAY(),"Y")Complete years between dates
=DATEDIF(A1,B1,"M")Complete months between dates
=DATEDIF(A1,B1,"Y")&" years, "&DATEDIF(A1,B1,"YM")&" months"Creates age string
- •Not shown in function wizard but works
- •Start date must be before end date
- •Use for calculating ages
- •Not realizing DATEDIF is undocumented in Excel's own function list (it doesn't show up in autocomplete), which makes people assume it doesn't exist or is unreliable - it works fine, it's just not officially listed
- •Using the "MD" unit expecting a reliable day-difference-ignoring-months result - Microsoft's own documentation warns this specific unit can return unexpected results in edge cases
- •Putting the dates in the wrong order - DATEDIF requires the earlier date first and the later date second, and reversing that order returns a #NUM! error
YEARFRACCalculates the difference between two dates as a fraction of a year, a different angle on date differences.TODAYOften used as the end date argument in a DATEDIF formula calculating age or tenure.EDATEMoves a date forward or backward by months, the reverse operation of measuring a month difference with DATEDIF.Why doesn't DATEDIF show up when I start typing it in a cell?
It's an undocumented function that Microsoft never added to Excel's autocomplete or official function list, even though it still works correctly. Just type it out manually.
What happens if I put the end date before the start date?
DATEDIF returns a #NUM! error - the first date argument must be earlier than or equal to the second.
What's the safest way to calculate someone's age with DATEDIF?
Use the "Y" unit: DATEDIF(birth_date,TODAY(),"Y") gives the number of complete years, which is the standard way to calculate age.
Need to translate a formula using DATEDIF?
Use our translator to convert your complete formula