↑ Return to Date & Time

DATEDIF Function

The function DATEDIF calculates the difference between 2 dates (day, month or year).



Suppose the date of 28/07/2009 and 05/08/2009. Between these two dates, there are only a gap of nine days. But there is a switch from a calendar month. So if we use the MONTH formula, the result équal 1 and it is completely false !


As the example has shown, a difference between two dates is not an easy calculation. First of all what kind of difference do we want to calculate? A difference on the months, on the days or on the years? To perform these calculations, Excel has an i-n-c-r-e-d-i-b-l-e function to calculate the  differences between the dates but this function is hidden 😕

True ! If you open the function dialog box, you will not see the function DATEDIF in the list of Excel functions but this function works perfectly.

The DATEDIF function

This function requires three parameters:

  • a date
  • a second date (necessarily greater than the first date)
  • a parameter to return the gap.

No difficulties for the first 2 parameters so let's see the last one. The third parameter is really important because it precise if you want to calculate a gap in days, months or years. But also if you want to calculate a number of months inside a year (or a number of days in a month).

The following table presents different values for the third parameter (should always set the third parameter in quotation marks). In this example, you have the different result between the date in C1 and C2 in function of the 3rd parameters.




Now, let's solve the problem. You have a document with birthdays and you want to know the age of each people.

  • To calculate the number of years, you will specify the parameter "y".
  • To calculate the number of month, in the current year "ym"
  • To calculate the number of days in the current month "md"

The formulas in C5, D5 and E5 are

= DATEDIF($B5,$D$1,"y")
= DATEDIF($B5,$D$1,"ym")
= DATEDIF($B5,$D$1,"md")


In the following worksheet, calculate for each person the gap in years, months in the current year and days in the month.


Skip to comment form

  1. John Heaton

    But datedif does not seem to be available in excel 2013

    1. Frédéric LE GUEN

      Yes it is ! How do think I have create the worksheet embedded in this page ?

      1. Denise

        The datedif function is not showing as an option in the Date functions in Excel 2013 where as it was available in the 2010 versions.

        1. Frédéric LE GUEN

          It’s a hidding function. Logic you don’t have any option

  2. Steve Chase

    very helpful post. Thank you

  3. Aleks

    Thank you!!!
    Very helpful post.

  4. Shawn Franklin

    I have a set of dates. I want to assign a color to each of these dates that are 0 days from todays date to 10 days to 15 days etc. How would I do that exactly?

  5. sbbj

    Dear sir, but datedif function some time also provide false results try this 1st dt 31-12-2005 2nd dt 30-6-2006 & try to calculate months it will produce result 5 instead of 6 pls explain

  6. Rachel

    how do I use datedif to calculate the days including the start and end date? not just the days inbetween two dates?

Leave a Reply