«

»

Difference between 2 dates - DATEDIF

Making a difference between 2 dates is not an easy task in Excel.

You must take in consideration if you want to return a result based on the gap of the months or based of the years, or both.

What is the problem?

Suppose that you have the following date

  • 28/07/2017
  • 05/08/2017

 

The result between this 2 dates is 9 days. But the months are not the same.

So if we use the MONTH function, the result is 1 and it's  wrong, wrong, WRONG!

=MONTH(B2)-MONTH(B1)

 

How to solve the problem?

The previous example showed that a difference between 2 dates is NEVER an easy calculation.

But also, how do we want to return the result? By year? By month? By month within a year?

To perform all these calculations, Excel has an incredible function to realize all the different calculation between 2 dates. It is the DATEDIF function.

Unfortunately, this function is hidden (wh ... what ???) 😲😲😲

To find out, open the help for this function. You won't see it in the list of function but it works perfectly (they're weird at Microsoft ????)

The DATEDIF function

This function requires three parameters:

  • One date
  • A second date (necessarily greater than the first date)
  • The type of calculation, the most important parameter. ⚠⚠⚠

=DATEDIF(date1,date2,parameter)

The 2 first parameters of the function are easy to understand. The third one is by far the most important because it will specify the type of calculation that you want to return.

The following table shows the different result in function of the last parameter.

Explanation of the values of the third parameter

The first 3 codes are easy to understand

  • d (day) calculates the difference in number of days between the 2 dates.
  • m (month) calculates the difference in number of months
  • y (year) calculates the difference in number of years

The other codes return a value within month or day limits

  • ym will return the number of months inside a year. Possible value between 0 and 11.
  • md will return a value between 0 and 30.
  • yd is less useful. This code returns the number of days in a year. Possible value between 0 and 364.

Calculation of employee seniority

In column B, we have the hiring dates of our employees. The second date is the current day date returned by the TODAY function.

We have the following formulas

  • For the years
    =DATEDIF($B5,$D$1,"y")
  • For the months inside a year
    =DATEDIF($B5,$D$1,"ym")
  • For the days inside a month
    =DATEDIF($B5,$D$1,"md")

Related articles


Have a look at these other articles that could help you in your work

Permanent link to this article: https://www.excel-exercise.com/difference-between-2-dates-datedif/


Leave a Reply

Your email address will not be published. Required fields are marked *