# DATEDIF Function

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

## Principle

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 !

=MONTH(B1)-MONTH(B2)

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.
=DATEDIF(date1,date2,parameter)

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

## Exercise

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

1. ##### Anonymous

Thank you work great in excel 2013

2. ##### Anonymous

DATEDIF always gives completed years/months/days

3. ##### Anonymous

Thank you so much!!!

4. ##### Surajit Roy

I want to learn Index match please inform me

5. ##### Anonymous

Dear Sir,

Thanks for posting
But I Found error, when the second date is end of February

6. ##### Anonymous

Sir,
What is meant by, number of months/days in the “current year”.

7. ##### Rachel

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

8. ##### 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

9. ##### 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?

Thank you!!!

11. ##### Steve Chase

very helpful post. Thank you

12. ##### 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