Excel DATEDIF Function: Syntax, Examples and Tips

Part of the free Module 5: Excel Formulas and Functions · Function 19 of 105 · Full Excel course

The Excel DATEDIF function returns the number of complete days, months or years between two dates, or the remainder after removing whole years or months. It is the standard way to calculate ages and service lengths. DATEDIF is an undocumented compatibility function inherited from Lotus 1-2-3: it works in every version of Excel but does not appear in the formula list or IntelliSense, so you must type it in full.

Table of Contents

Syntax

=DATEDIF(start_date, end_date, unit)

Arguments

Argument	Required / Optional	Meaning
start_date	Required	The earlier date. It must be on or before end_date, otherwise the function returns #NUM!.
end_date	Required	The later date.
unit	Required	A text code in quotes that sets what is counted: “y”, “m”, “d”, “ym”, “md” or “yd” (see table below).

Unit codes

Unit	Returns	Example: 15 Mar 2020 to 4 Sep 2026
“y”	Complete years	6
“m”	Complete months	77
“d”	Days	2364
“ym”	Months remaining after complete years	5
“md”	Days remaining after complete months (unreliable, see tips)	20
“yd”	Days remaining after complete years, ignoring the year	173

How DATEDIF works

DATEDIF measures the interval between the two dates; it does not count the dates inclusively. =DATEDIF("1-Jan-2026","2-Jan-2026","d") returns 1, not 2. With “y” and “m”, only completed periods count, so a person born on 5 September 2000 is 25 on 4 September 2026 and turns 26 the next day. The result is a plain number, so the cell should be in General or Number format, not a date format.

Step-by-step example: age in years, months and days

Enter a birth date in A2, for example 15 March 1990.
In B2 type =DATEDIF(A2,TODAY(),"y"). Because DATEDIF is hidden, no tooltip appears while typing; that is normal.
Press Enter. B2 shows the age in complete years.
In C2 enter =DATEDIF(A2,TODAY(),"ym") for the extra months, and in D2 =DATEDIF(A2,TODAY(),"md") for the extra days.
Combine them in E2: =B2&" years, "&C2&" months, "&D2&" days".

Practical use cases

1. Length of service for HR reports

=DATEDIF(B2,TODAY(),"y")&" yrs "&DATEDIF(B2,TODAY(),"ym")&" mths"

2. Months between invoice and payment

=DATEDIF(C2,D2,"m")

3. Days until a deadline, without errors for past dates

=IF(E2<TODAY(),"Overdue",DATEDIF(TODAY(),E2,"d"))

4. Contract term check

=DATEDIF(A2,B2,"m")>=12

Returns TRUE when the contract runs for at least twelve complete months.

Common mistakes and tips

#NUM! error: start_date is later than end_date. Swap the arguments or wrap them in MIN and MAX.
#NAME? error: the unit is not in quotes, or the function name is misspelled. Excel does not autocomplete DATEDIF.
“md” can be wrong: Microsoft documents that the “md” unit may return a negative or incorrect number for some month-end combinations. For the day remainder, a safer alternative is =B2-DATE(YEAR(B2),MONTH(B2)-DATEDIF(A2,B2,"m"),DAY(A2)) or simply report years and months.
Result shown as a date: the cell inherited a date format. Set it to General.
Text dates: DATEDIF accepts text such as “2026-09-04”, but cell references to real dates are more reliable.

DATEDIF compared with YEARFRAC, DAYS and subtraction

Simple subtraction, =B2-A2, and the DAYS function both give the number of days and are all you need for day counts. YEARFRAC returns the interval as a decimal number of years, for example 6.47, which suits interest and pro-rata calculations but must be wrapped in INT to give complete years. DATEDIF is the only built-in function that returns complete months or the year-and-month remainder directly, which is why it remains the standard for ages and tenure despite its hidden status. In Excel 365 and Google Sheets it continues to work; Google Sheets also lists it in the function help.

Related functions

YEARFRAC: returns the difference as a fraction of a year.
DAYS: returns the number of days between two dates.
TODAY: supplies the current date as the end date.
NETWORKDAYS: counts working days between two dates.
See all lessons in the Excel Formulas course.

Frequently asked questions

Why does DATEDIF not appear in the Excel function list?

DATEDIF is an undocumented function kept for compatibility with Lotus 1-2-3. It works in all versions, but you have to type the full name because there is no IntelliSense entry.