ExcelDateIntermediate

DATEDIF

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

Read the syntaxReview worked examplesOpen the spreadsheet app
=DATEDIF(start_date, end_date, unit)
Open Spreadsheet Explorer

This static page keeps the function indexed for search, while the spreadsheet app handles interactive exploration and saved formulas.

What the function does

Overview

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

DATEDIF is a legacy compatibility function originally designed for Lotus 1-2-3. It remains in Excel to support old workbooks but is famously 'hidden'—it does not appear in the formula autocomplete list or function tooltips. It calculates the elapsed time between two dates using specific units: 'Y' (years), 'M' (months), 'D' (days), 'YM' (months excluding years), 'YD' (days excluding years), and 'MD' (days excluding months and years). A critical best practice is to avoid using the 'MD' unit, as Microsoft has documented known bugs where it can return zero or negative results. Always ensure the ate is earlier than the ate to avoid calculation errors. For simple day counts, standard subtraction (B2-A2) is generally preferred over DATEDIF.

Quick reference

Syntax

=DATEDIF(start_date, end_date, unit)

Inputs

Arguments

start_dateThe earlier date
Example: A2
end_dateThe later date
Example: TODAY()
unitResult type: "Y" (Years), "M" (Months), "D" (Days), "YM" (Months ignoring years)
Example: "Y"

Formula patterns

Examples

1

Calculate total years of employment

=DATEDIF(A2, B2, "Y")
hrpayrolltenure
2

Find the month component of an age

=DATEDIF(A2, B2, "YM")
demographicsreporting
3

Count total days between milestones

=DATEDIF(A2, B2, "D")
project managementdeadlines

Avoid these issues

Common Errors

1

#NUM!

Cause: The start_date is chronologically later than the end_date.

Fix: Swap the arguments so the earlier date is in the first position, or use =DATEDIF(MIN(A2, B2), MAX(A2, B2), "Y").

2

#VALUE!

Cause: The date arguments are provided as text that Excel cannot interpret as a valid date format.

Fix: Convert the input cells to proper Date formats or use the DATE function to build the arguments.

Platform support

Compatibility

Excel 2007+Excel-first

This is a hidden legacy function; type it manually.

Source: Microsoft Support

Common questions

Frequently Asked Questions

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

ate: The earlier date ate: The later date unit: Result type: "Y" (Years), "M" (Months), "D" (Days), "YM" (Months ignoring years)

#NUM!: Swap the arguments so the earlier date is in the first position, or use =DATEDIF(MIN(A2, B2), MAX(A2, B2), "Y"). #VALUE!: Convert the input cells to proper Date formats or use the DATE function to build the arguments.