Finding the difference between dates with the DATEDIF function
This walkthrough will teach you how to make use of the DATEDIF
function in Excel.
=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.
- 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.
- 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).