Quick Navigation

DATEDIF Function

Excel 2007+

Summary

The DATEDIF function calculates the precise difference between two dates in years, months, or days. It's particularly useful for age calculations, service tenure tracking, and project duration analysis, offering specialized units beyond simple date subtraction.

Syntax

DATEDIF(start_date, end_date, unit)

Parameters

Parameter	Type	Required	Description
start_date	`Date`	Yes	The starting date of the period. Can be date values, text strings in quotes, serial numbers, or cell references.
end_date	`Date`	Yes	The ending date of the period. Must be after or equal to start_date.
unit	`Text`	Yes	Text code specifying the unit: "Y" (years), "M" (months), "D" (days), "MD" (days ignoring months/years), "YM" (months ignoring days/years), "YD" (days ignoring years).

Using the DATEDIF Function

DATEDIF excels at calculating age, employment duration, and time spans with specific units. Unlike simple subtraction (which gives total days), DATEDIF provides complete years/months and specialized differences, making it perfect for HR reports, project timelines, and financial modeling.

Common DATEDIF Examples

Calculate Age in Complete Years

=DATEDIF("1/15/1985", TODAY(), "Y")

Returns the person's age in complete years from Jan 15, 1985 to today.

Months Between Project Start and End

=DATEDIF(B2, C2, "M")

Shows complete months between project start (B2) and end dates (C2).

Days Ignoring Year Difference

=DATEDIF("6/1/2023", "8/15/2024", "YD")

75 days between June 1 and August 15, ignoring the year difference.

Employee Service Years

=DATEDIF(D2, TODAY(), "Y") & " years, " & DATEDIF(D2,TODAY(),"YM") & " months"

Displays service length as "X years, Y months" from hire date in D2.

Frequently Asked Questions

It's a hidden legacy function for Lotus 1-2-3 compatibility. Type it manually - it works in all Excel versions.

Returns #NUM! error. Ensure end_date >= start_date.

Microsoft advises against 'MD' due to known issues. Use EOMONTH+DAY workaround instead.

Yes, since Excel 2000, but undocumented. Works consistently across versions.

Common Errors and Solutions

#NUM!

Cause: start_date later than end_date

Solution: Verify dates are in correct order (start before end)

#VALUE!

Cause: Invalid date format or non-date values

Solution: Ensure both dates are valid Excel dates using DATEVALUE if needed

#NAME?

Cause: Unit text not in quotes

Solution: Use "Y", "M", etc. with double quotes

0 result unexpectedly

Cause: Using incomplete periods when exact days wanted

Solution: Use "D" for total days instead of "Y" or "M"

Notes

Microsoft warning: DATEDIF may give incorrect results in edge cases
For simple day counts, use end_date - start_date instead
'MD' unit has known limitations - use EOMONTH(DATE(YEAR(end_date),MONTH(end_date),1),0)-start_date+1 workaround
Dates stored as serial numbers: Jan 1, 1900 = 1
Combine units: =DATEDIF(A1,B1,"Y")&"y "&DATEDIF(A1,B1,"YM")&"m"

Compatibility

Available in: Excel 2000, Excel 2007, Excel 2010, Excel 2013, Excel 2016, Excel 2019, Excel 2021, Excel 365

Not available in:

Content last reviewed: December 9, 2025
Update frequency: As needed
Excel versions tested: Excel 2007+