DATEDIF function in Excel

DATEDIF returns the number of complete years, months or days between two dates.

How old is someone? How long has an employee served? How many months has a loan been running? Subtracting two dates gives days, and turning days into years by dividing by 365 is slightly wrong because of leap years. DATEDIF counts completed years and months the way people do.

Syntax

=DATEDIF(start_date, end_date, unit)

Argument	What it is
`start_date`	The earlier date.
`end_date`	The later date.
`unit`	"Y" complete years, "M" complete months, "D" days, "YM" months left after the complete years, "MD" days left after the complete months, "YD" days left after the complete years.

Examples

Each result below is what Excel itself showed for the formula on the sample sheet.

Sample sheet: Staff with their date of birth and date of joining, and a short list of holidays.

	A	B	C	D
1	Name	Born	Joined	Holiday
2	Anita	19-Jul-1988	01-Apr-2019	26-Jan-2026
3	Rohit	30-Nov-1992	15-Jun-2020	04-Mar-2026
4	Deepa	29-Feb-1996	01-Sep-2021	15-Aug-2026
5	Manoj	05-Dec-1999	10-Jan-2022	02-Oct-2026

=DATEDIF(B2,DATE(2026,11,12),"Y")
Result: 38
Anita's age in completed years on 12 November 2026. She was born on 19 July 1988.
=DATEDIF(B3,DATE(2026,11,12),"Y")
Result: 33
Rohit was born on 30 November 1992. His birthday has not yet come in 2026, so he is still 33.
=DATEDIF(C2,DATE(2026,11,12),"M")
Result: 91
Complete months of service since 1 April 2019.
=DATEDIF(C2,DATE(2026,11,12),"D")
Result: 2782
The number of days, the same as subtracting the dates.
=DATEDIF(C2,DATE(2026,11,12),"YM")
Result: 7
Months left over after the complete years.
=DATEDIF(C2,DATE(2026,11,12),"Y")&" years, "&DATEDIF(C2,DATE(2026,11,12),"YM")&" months"
Result: 7 years, 7 months
Length of service written out in words.
=DATEDIF(B4,DATE(2026,11,12),"Y")
Result: 30
Deepa was born on 29 February 1996, a leap day.
=DATEDIF(DATE(2026,11,12),C2,"Y")
Result: #NUM!
The dates the wrong way round, later date first: #NUM!.

A function Excel does not advertise

DATEDIF does not appear in Excel's list of functions, and nothing pops up to help when you type it. It has been kept for compatibility with older spreadsheet programs. It works in every version. You must type the name and all three arguments yourself.

The units

Unit	Counts	Example: 1 Apr 2019 to 12 Nov 2026
`"Y"`	complete years	7
`"M"`	complete months	91
`"D"`	days	2782
`"YM"`	months beyond the complete years	7
`"MD"`	days beyond the complete months	11
`"YD"`	days beyond the complete years	225

"Complete" is the key word. A year counts only once the anniversary has arrived. That is why Rohit, whose birthday is at the end of November, is one year younger on 12 November than a rough calculation would say.

Age

With the date of birth in B2:

=DATEDIF(B2, TODAY(), "Y")

The age goes up by one on each birthday, with no further work. For an age on a particular day, such as the cut-off date of an examination or a school admission, put that date in place of TODAY.

Years, months and days together, as some forms ask:

=DATEDIF(B2,TODAY(),"Y")&" years "&DATEDIF(B2,TODAY(),"YM")&" months "&DATEDIF(B2,TODAY(),"MD")&" days"

Length of service

The same formulas with the date of joining give service in years and months, as in the sixth example. Gratuity, leave entitlement and increments often depend on completed years, which is exactly what "Y" counts.

The earlier date goes first

If the start date is later than the end date, the result is #NUM!, as the last example shows. Excel does not swap them for you. When either order is possible, wrap the dates: =DATEDIF(MIN(A2,B2),MAX(A2,B2),"D").

A known fault in "MD"

Microsoft's own documentation warns that the "MD" unit can give a wrong result, even a negative number, for some pairs of dates, typically when the start day is late in a month and the end month is shorter. If you need the leftover days and they must be right, work them out another way: =end - EDATE(start, DATEDIF(start, end, "M")). This subtracts the date that lies exactly the completed months after the start.

Alternatives

Days: simply subtract, =B2-A2.
A fraction of a year: =YEARFRAC(A2,B2) gives something like 7.62, useful for interest and pro-rata sums.
Working days: NETWORKDAYS.

Common mistakes

Dates in the wrong order.
The unit without quotation marks. It must be "Y", not Y.
Dates stored as text, giving #VALUE!.
Trusting "MD" without checking.
Expecting the function to appear in Excel's suggestions. It will not. Type it in full.

In Google Sheets

DATEDIF is a documented, ordinary function in Google Sheets, with the same six units.

Related functions

Every result on this page was calculated in Microsoft Excel (version 16.0, build 20326) on 12 November 2026, not typed by hand.

This is an independent help site. It is not connected with or approved by Microsoft. Excel and Microsoft 365 are trademarks of Microsoft Corporation, named here only to say which program a formula is for. The people and figures in the sample sheets are made up.