DATEDIF In Excel 2010


The DATEDIF function in Microsoft Excel calculates the difference, or interval, between two dates. This difference can be expressed in a variety of ways. The function takes the form

=DATEDIF(Date1, Date2, Interval)

where Date1 and Date2 are the two dates and Interval defines how the date difference should be returned. Interval must be enclosed in quotes like this:

=DATEDIF(Date1, Date2, “d”)

These are the errors that DATEDIF may return if you get something wrong:

  • #NUM error if Date1 is later than Date2
  • #VALUE error if either Date1 or Date2 are invalid dates
  • #NUM error if the Interval specified is not one of the allowed values

The Interval must be one of the following values:

Interval Use
m Months: the number of whole calendar months between the two dates
d Days: the number of days between the dates
y Years: the number of whole calendar years between the dates
ym Months In Same Year: the number of months between the two dates if they were in the same year
yd Days In Same Year: the number of days between the two dates if they were in the same year
md Days In Same Month And Year: the number of days between the two dates if they were in the same month and year

DATEDIF Examples

Try and predict what the result of the following examples will be:

DATEDIF Result
=DATEDIF(01/01/2010, 01/06/2010, “d”) 151
=DATEDIF(01/01/2010, 15/01/2010, “d”) 14
=DATEDIF(01/01/2010, 01/06/2010, “m”) 5
=DATEDIF(01/01/2010, 15/01/2010, “m”) 0
=DATEDIF(01/01/2009, 01/06/2010, “m”) 17
=DATEDIF(18/08/2008, 01/03/2010, “y”) 2
=DATEDIF(01/01/2010, 31/12/2010, “y”) 0
=DATEDIF(01/01/2008, 01/06/2010, “ym”) 5
=DATEDIF(23/04/2003, 31/05/2005, “yd”) 38
=DATEDIF(17/02/1974, 28/01/1998, “md”) 11

Sponsors