Difference between 2 dates – DATEDIF

Difference between 2 dates – DATEDIF
Last Updated on 10/06/2023
Reading time: 2 minutes

Presentation

Calculate the 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?

Let's suppose that you have these 2 dates

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

DATEDIF is a hidden function 🤨🤔

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?
  • Or by month in 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 is not listed in the dialog box

The DATEDIF function

This function requires 3 arguments:

  1. The first date
  2. The second date (necessarily greater than the first date)
  3. Type of calculation

=DATEDIF(date1,date2,parameter)

Results return by each parameter of the DATEDIF function

The 3rd parameter of the function (the most important)

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.

Calculate the seniority

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

Seniority of the employees calculated with DATEDIF
Calculate the age of each employees

For each columns, 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")

2 Comments

  1. Mahbub
    31/12/2020 @ 18:12

    How can i sum 1 year 9 month 29 days with 1year 5 month 5 days

    Reply

    • Oliver Street
      25/03/2023 @ 16:52

      For date calculation of the type you ask there can be no answer unless the calendar is simplified so that every year has 360 days and every month has 30 days. The result will be useful for many purposes, but not for adding a date span to a calendar date.

      days = mod(day1 + day2 , 30)
      months = mod(month1 + month 2 + int( (day1 + day2)/30), 12)
      years = year1 + year2 + int( (month1 + month2 + int( (day1 + day2)/30 ) )/12)

      for the example;
      days = mod(29+5,30) = mod(34,30) = 4
      months = mod(9 + 5+ int( (29 + 5)/30), 12) = mod(9 + 5+ int( 34/30), 12) = mod(9 + 5+ 1, 12) = mod(9 + 5+ 1, 12) = mod(15,2) = 3
      years = 1 + 1 + int( (9 + 5+ int( (29 + 5)/30 ) )/12) = 1+1+int((9+5+1)/12) = 1+1+1 = 3

      3 years 3 months 4 days

      The date spans being added should be computed as a date difference using DAYS360(start, end)
      the resulting span is;

      days = mod(DAYS360(start, end),30)
      months = mod(int(DAYS360(start, end)/30),12)
      years = int(int(DAYS360(start, end)/30)/12)

      Reply

Leave a Reply

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

Difference between 2 dates – DATEDIF

Reading time: 2 minutes
Last Updated on 10/06/2023

Presentation

Calculate the 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?

Let's suppose that you have these 2 dates

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

DATEDIF is a hidden function 🤨🤔

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?
  • Or by month in 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 is not listed in the dialog box

The DATEDIF function

This function requires 3 arguments:

  1. The first date
  2. The second date (necessarily greater than the first date)
  3. Type of calculation

=DATEDIF(date1,date2,parameter)

Results return by each parameter of the DATEDIF function

The 3rd parameter of the function (the most important)

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.

Calculate the seniority

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

Seniority of the employees calculated with DATEDIF
Calculate the age of each employees

For each columns, 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")

2 Comments

  1. Mahbub
    31/12/2020 @ 18:12

    How can i sum 1 year 9 month 29 days with 1year 5 month 5 days

    Reply

    • Oliver Street
      25/03/2023 @ 16:52

      For date calculation of the type you ask there can be no answer unless the calendar is simplified so that every year has 360 days and every month has 30 days. The result will be useful for many purposes, but not for adding a date span to a calendar date.

      days = mod(day1 + day2 , 30)
      months = mod(month1 + month 2 + int( (day1 + day2)/30), 12)
      years = year1 + year2 + int( (month1 + month2 + int( (day1 + day2)/30 ) )/12)

      for the example;
      days = mod(29+5,30) = mod(34,30) = 4
      months = mod(9 + 5+ int( (29 + 5)/30), 12) = mod(9 + 5+ int( 34/30), 12) = mod(9 + 5+ 1, 12) = mod(9 + 5+ 1, 12) = mod(15,2) = 3
      years = 1 + 1 + int( (9 + 5+ int( (29 + 5)/30 ) )/12) = 1+1+int((9+5+1)/12) = 1+1+1 = 3

      3 years 3 months 4 days

      The date spans being added should be computed as a date difference using DAYS360(start, end)
      the resulting span is;

      days = mod(DAYS360(start, end),30)
      months = mod(int(DAYS360(start, end)/30),12)
      years = int(int(DAYS360(start, end)/30)/12)

      Reply

Leave a Reply

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