DATEDIF in Excel: Calculate Difference Between Dates
Calculates the number of days, months, or years between two dates.
How to Calculate Date Differences with DATEDIF
The DATEDIF function computes the difference between two dates in days, completed months, or completed years.
Syntax & Unit Codes
=DATEDIF(start_date, end_date, unit)
"Y": Number of complete years"M": Number of complete months"D": Number of days"YM": Months ignoring years (useful for "X years and Y months")
Example: Age Calculation
=DATEDIF(B2, TODAY(), "Y") & " Years, " & DATEDIF(B2, TODAY(), "YM") & " Months"
Common Errors & Fixes
#NUM! error
Causes:- Start date is later than end date. DATEDIF does not handle reversed dates.
Fixes:- Use =IF(start > end, -DATEDIF(end, start, unit), DATEDIF(start, end, unit)) for signed results, or swap the arguments.
Frequently Asked Questions
Why is DATEDIF not showing up in my Excel?
DATEDIF is a hidden function in modern Excel. It still works but does not appear in the formula autocomplete. Type =DATEDIF( manually and it will work.
What DATEDIF units are available?
"Y" for complete years, "M" for complete months, "D" for days, "YM" for months ignoring years, "YD" for days ignoring years, and "MD" for days ignoring months and years.
Does DATEDIF work in Google Sheets?
Yes, DATEDIF works in Google Sheets with the same syntax. All unit types (Y, M, D, YM, YD, MD) are supported.
Related Formulas
Explore related formula generators to solve similar problems
🛠️ Related Tools
Want to become an Excel Pro?
Stop searching for formulas. Master Excel in 30 days with this top-rated course.
Learn More