fx
FunctionPedia
Quick Answer

DATEDIF Function

Returns the difference between two dates in years, months, or days.

✓ Excel✓ Google SheetsExcel All versions (legacy, undocumented function)

Syntax

DATEDIF
(start_date, end_date, unit)

Parameters

ParameterDescriptionRequired
start_dateThe earlier date. Must be less than or equal to end_date.Required
end_dateThe later date.Required
unitText code: "Y" full years, "M" full months, "D" days, "MD" days excluding months/years, "YM" months excluding years, "YD" days excluding years.Required

Basic Example

Full years between two dates

=DATEDIF(DATE(2020, 1, 1), DATE(2024, 6, 1), "Y")
Result4

From Jan 1 2020 to Jun 1 2024 there are 4 complete years (the sixth month is not yet reached).

Advanced Examples

Example 1: Total complete months

Measuring tenure in months

Unit "M" counts full months

=DATEDIF(DATE(2020, 1, 1), DATE(2024, 6, 1), "M")
Result: 53
Four years (48 months) plus five months (Jan to Jun) equals 53 full months.

Example 2: Remaining months after full years

Age in years and months

Unit "YM" ignores the year count

=DATEDIF(DATE(2021, 3, 15), DATE(2024, 7, 20), "YM")
Result: 4
After removing the 3 full years (to 2024-03-15), 4 full months remain until July 20.

How DATEDIF Works

DATEDIF compares the two date serials. For Y/M it counts whole years or months, while the remainder units (YM, YD, MD) compute what is left after stripping the larger unit. It is an undocumented compatibility function carried over from Lotus 1-2-3.

1
Enter start and end dates
Make sure start_date is on or before end_date.
2
Pick the unit
Use "Y", "M", "D", "MD", "YM", or "YD" in quotes.
3
Read the difference
The function returns an integer according to the unit.

Important Notes & Limitations

  • It is undocumented, so it does not appear in formula autocomplete or help.

  • start_date must be less than or equal to end_date, otherwise it returns #NUM!.

  • The "MD" unit has a long-standing Microsoft bug that can return incorrect or negative values in some date combinations; prefer "D" or compute days manually when precision matters.

Common Errors & Fixes

#NUM!start_date is later than end_date, or the unit text is invalid.

Fix: Ensure start_date <= end_date and use one of the six valid unit codes.

#VALUE!A non-date value or an unrecognized unit code was supplied.

Fix: Pass real date serials and a valid unit string in quotes.

Download Practice File

Practice DATEDIF with Real Data

Download a sample CSV file with pre-populated data and practice exercises for the DATEDIF function. Works in both Excel and Google Sheets.

Works in Google SheetsCompatible with ExcelIncludes exercises

File format: CSV (comma-separated values) - opens in Excel, Google Sheets, and all spreadsheet apps

Related Tutorials

Calculate Age From Birthday

Learn how to calculate age from birthday in Excel and Google Sheets using DATEDIF, YEAR and TODAY — get exact years, or years, months and days, that update automatically.

Frequently Asked Questions

Why can't I find DATEDIF in the function list?
It is a legacy compatibility function that is supported but intentionally not shown in autocomplete.
What is wrong with the MD unit?
Microsoft documents a bug in MD that can return incorrect/negative results; verify critical calculations another way.
Which unit gives total days?
Use "D" for the full day count; "YD" ignores the year difference.