DATEDIF

Calculate the difference between two dates as complete years, months, or days using DATEDIF.

Date & Time
|
Excel All versions
|
Google Sheets Supported

Spreadsheet editor

Syntax

=DATEDIF(start_date, end_date, unit) Returns: Number

Arguments

Argument Required Description
start_date Yes The starting date of the period to measure.
end_date Yes The ending date of the period. Must be on or after start_date.
unit Yes A text code that determines what to count: "Y" (full years), "M" (full months), "D" (total days), "MD" (days ignoring months/years), "YM" (months ignoring years/days), "YD" (days ignoring years).

About

DATEDIF returns the number of complete years, months, or days between two dates. The third argument is a unit code that controls what gets counted: "Y" for full years, "M" for full months, "D" for total days. Three additional codes let you extract partial differences, such as months remaining after whole years ("YM") or days remaining after whole months ("MD").

The most common use is calculating age or tenure. For example, =DATEDIF(A2, TODAY(), "Y") gives someone's age in complete years from their birthdate. You can also combine multiple DATEDIF calls to display results like "2 years, 3 months" by using "Y" and "YM" together.

DATEDIF is a legacy function that doesn't appear in Excel's autocomplete, but it works in all versions. One known issue: the "MD" unit can return incorrect or negative results in some edge cases, so for total day counts, subtraction or DAYS is more reliable. The function returns a #NUM! error if start_date is later than end_date.

Examples

Date order matters

See what happens when start_date comes after end_date. Try fixing the reversed dates in row 3. Changing B3 to a later date clears the #NUM! error right away.

Spreadsheet editor

Why "MD" can be misleading

Notice that "MD" returns 0 for dates exactly one year apart, even though 365 days have passed. Compare columns C and D. For accurate day counts, use simple subtraction or the "D" unit instead.

Spreadsheet editor

Building a readable tenure string

Watch how two DATEDIF calls combine into a human-friendly result like "3 yr 7 mo". Change any start date and the tenure updates immediately. The "YM" unit handles the remaining months after whole years are counted.

Spreadsheet editor

Watch out for

⚠

Start date is after end date

If start_date is later than end_date, DATEDIF returns a #NUM! error. This often happens when dates come from user input or when TODAY() is used as the start date instead of the end.

→ Make sure start_date is always the earlier date. Wrap the formula with IFERROR to handle cases where the order might vary.

⚠

The "MD" unit gives inaccurate results

The "MD" unit, which counts days while ignoring months and years, can return negative numbers or wrong values in certain edge cases. Microsoft has acknowledged this as a known issue.

→ Avoid "MD" for calculations where accuracy matters. If you need the total number of days between two dates, use simple subtraction or the DAYS function instead.

⚠

Function doesn't appear in autocomplete

Typing =DATEDIF in a cell won't trigger Excel's autocomplete suggestions or show argument hints. This makes it easy to accidentally misspell the name or mix up the argument order.

→ Type the full function manually: =DATEDIF(start_date, end_date, unit). Remember that start_date always comes first.

⚠

Dates stored as text

If your date cells contain text strings instead of real date values, DATEDIF may return an error or give unexpected results.

→ Check that cells are formatted as dates, not text. Use DATEVALUE to convert text representations of dates into serial numbers that DATEDIF can work with.

Tips & notes

Unit codes are not case-sensitive, so "Y", "y", "YD", and "yd" all work the same way. DATEDIF is not listed in Excel's official function reference, but it is fully supported across all modern versions.

Common questions

Why doesn't DATEDIF show up when I type it in Excel?

DATEDIF is a legacy function that Excel inherited from Lotus 1-2-3. Microsoft kept it for backward compatibility but removed it from the official function list. It still works in all versions. Just type the full formula: =DATEDIF(start_date, end_date, unit).

What unit codes can I use with DATEDIF?

There are six unit codes: "Y" (complete years), "M" (complete months), "D" (total days), "MD" (days ignoring months and years), "YM" (months ignoring years and days), and "YD" (days ignoring years). For most tasks, stick with "Y", "M", and "D". Avoid "MD" due to known accuracy issues.

Can I use DATEDIF to calculate someone's age?

Yes. =DATEDIF(birthdate, TODAY(), "Y") returns the current age in complete years. To show both years and months, combine it with a second call: =DATEDIF(birthdate, TODAY(), "YM") gives the remaining months after whole years.