Finding the difference between dates with the DATEDIF function

October 2017


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).
Published by jak58. Latest update on November 6, 2016 at 02:09 PM by owilson.
This document, titled "Finding the difference between dates with the DATEDIF function," is available under the Creative Commons license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM (ccm.net).