## 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 ****! **

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

## Exercise

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

## 15 comments

Skip to comment form ↓

## Anonymous

March 14, 2016 at 9:46 am (UTC 0) Link to this comment

Thank you work great in excel 2013

## Anonymous

March 4, 2016 at 2:49 pm (UTC 0) Link to this comment

DATEDIF always gives completed years/months/days

## Anonymous

January 27, 2016 at 8:11 am (UTC 0) Link to this comment

Thank you so much!!!

Very helpful post.

## Surajit Roy

November 24, 2015 at 10:04 am (UTC 0) Link to this comment

I want to learn Index match please inform me

## Anonymous

September 8, 2015 at 7:24 am (UTC 0) Link to this comment

Dear Sir,

Thanks for posting

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

## Anonymous

September 1, 2015 at 6:05 am (UTC 0) Link to this comment

Sir,

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

## Rachel

August 24, 2015 at 3:28 pm (UTC 0) Link to this comment

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

## sbbj

August 20, 2015 at 7:39 am (UTC 0) Link to this comment

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

## Shawn Franklin

March 6, 2015 at 7:16 pm (UTC 0) Link to this comment

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?

## Aleks

March 4, 2015 at 12:48 pm (UTC 0) Link to this comment

Thank you!!!

Very helpful post.

## Steve Chase

September 21, 2014 at 3:21 am (UTC 0) Link to this comment

very helpful post. Thank you

## John Heaton

September 21, 2014 at 12:29 am (UTC 0) Link to this comment

But datedif does not seem to be available in excel 2013

## Frédéric LE GUEN

September 21, 2014 at 9:57 pm (UTC 0) Link to this comment

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

## Denise

November 5, 2014 at 6:58 pm (UTC 0) Link to this comment

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.

## Frédéric LE GUEN

February 5, 2015 at 10:49 am (UTC 0) Link to this comment

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