Finding the difference between dates with the DATEDIF function

Ask a question

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).

Creating Shortcut key to merge cells
Excel - Comparing columns and returning value