How to use DATEDIF in Excel

DATEDIF is very useful for counting the number of whole calendar years, months and days between two dates. It is a hidden worksheet function and you will not find it in the list of functions but you can use it.

DATEDIF syntax

=DATEDIF(startDate,endDate, “period”)

You have to write this syntax right into the cell.

Period values

y	Number of whole calendar years (including leap year)
m	Number of whole calendar months
d	Number of days
ym	Number of whole months in an incomplete year
yd	Number of days in an incomplete year
md	Number of days in an incomplete month

DATEDIF errors

#NUM! – startDate is later than endDate
#VALUE – startDate or endDate is not a valid date

DATEDIF examples

Note: Dates in this example are in m/d/y format.

A1 cell: 09/01/2012
B1 cell: 11/29/2013

=DATEDIF(A1,B1, “y”) – return  1 (complete calendar year: 09/01/2012–09/01/2013)

=DATEDIF(A1,B1, “m”) – return  14 (complete calendar months: 09/01/2012–09/01/2013)

=DATEDIF(A1,B1, “d”) – return  454 (complete calendar days)

=DATEDIF(A1,B1, “ym”) – return  2 (complete months in incomplete year: 09/01/2013–11/01/2013)

=DATEDIF(A1,B1, “yd”) – return  89 (days in incomplete year: 09/01/2013–11/29/2013)

=DATEDIF(A1,B1, “md”) – return  28 (days in incomplete month: 11/01/2013–11/29/2013)

Year

A1 cell: 11/01/2012
B1 cell: 11/01/2013

=DATEDIF(A1,B1, “y”) – return  1

A1 cell: 11/01/2012
B1 cell: 10/31/2013

=DATEDIF(A1,B1, “y”) – return  0

Month

A1 cell: 07/01/2013
B1 cell: 11/01/2013

=DATEDIF(A1,B1, “m”) – return  4

A1 cell: 07/01/2013
B1 cell: 10/31/2013

=DATEDIF(A1,B1, “m”) – return  3

Day

A1 cell: 07/01/2013
B1 cell: 07/03/2013

=DATEDIF(A1,B1, “d”) – return  2

Number of years, months and days

A1 cell: 09/01/2012
B1 cell: 11/29/2013

=DATEDIF(A1,B1,”y”) … years (1)
=DATEDIF(A1,B1,”ym”) … months (2)
=DATEDIF(A1,B1,”md”) …days (28)

Want more? Try very nice article by Chip Pearson.