Finding the difference between dates with the DATEDIF function

December 2016


Finding the difference between dates with the DATEDIF function



This walkthrough will teach you how to make use of the DATEDIF function in Excel.

The Syntax

=DATEDIF(start date,end date,type of calculation)
  • Start date: Must be a valid date value.
  • End date: Must be a valid date value >q than beginning date.
  • The type of calculation to be performed:
    • 1 "y": difference in years (year).
    • 2 "m": the difference in months (month).
    • 3 "d": difference in days (day).
    • 4 "ym" difference in months, after subtracting the years.
    • 5 "yd" difference in days, after subtracting the years.
    • 6 "md" difference in days, after subtracting the years and months.

Example

  • In A1: 05/02/2007
  • In B1: 02/02/2009
    • 1 =DATEDIF(A1,B1,"y") => 1
    • 2 =DATEDIF(A1,B1,"m") => 23
    • 3 =DATEDIF(A1,B1,"d") => 728
    • 4 =DATEDIF(A1,B1,"ym") => 11
    • 5 =DATEDIF(A1,B1,"yd") => 362
    • 6 =DATEDIF(A1,B1,"md") => 28

If you combine 1, 4, 6 => 1 year 11 months 28 days.

Implemetation:

  • To use this function, make sure the end date is greater than start date, otherwise you will have the #NUM! error. The same applies if your calculation is not correct.
  • As you can see, you can use this funtion to calculate a precise time lapse or to manage a schedule.
  • In all cases, you get a number that must be called depending on the parameter: years, months, days.


Note that, this method is not 100% reliable
  • When you use start and end dates like: 01/09/13 and 31/12/13, the dateif function will return difference 3 months (instead of 4).

Related :

This document entitled « Finding the difference between dates with the DATEDIF function » from CCM (ccm.net) is made available under the Creative Commons license. You can copy, modify copies of this page, under the conditions stipulated by the license, as this note appears clearly.