The DATEDIF function in Excel

The DATEDIF function in Excel
Last Updated on 12/05/2024
Reading time: 2 minutes

The DATEDIF function in Excel calculates the difference between two dates. It often determines age, tenure, or periods between dates.

Calculating the difference between 2 dates is not easy in Excel. You must consider whether you want to return a result based on the gap of the months, 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 these 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

The DATEDIF function is hidden 🤨🤔

The previous example showed that calculating the difference between 2 dates is NEVER easy. But 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 calculations 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 functions, but it works perfectly (they're weird at Microsoft)

The DATEDIF function is not listed in the dialog box

The DATEDIF function in Excel

The DATEDIF function takes three arguments:

  1. Start_date

    This is the starting date of the period you want to calculate. This date should come before the End_date.

  2. End_date

    This is the ending date of the period. The End_date should be on or after the Start_date.

  3. Unit

    The Unit specifies the type of difference you want to calculate between the two dates

=DATEDIF(Start_date, End_date, Unit)

Results return by each parameter of the DATEDIF function

The 3rd argument of the function is the most important

The first 3 codes are easy to understand

  • "Y": Returns the number of complete years between the start and end dates.
  • "M": Returns the number of complete months between the dates.
  • "D": Returns the number of days between the dates

The other codes return a value within month or day limits

  • "MD": Returns the difference in days, ignoring months and years; results between 0 and 30
  • "YM" returns the difference in months, ignoring days and years; the result is between 0 and 11.
  • "YD": Returns the difference in days, ignoring years; results between 0 and 364

Calculate the seniority

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

Seniority of the employees calculated with DATEDIF

For each column, we have the following formulas

  • For the years

=DATEDIF($B5,$D$1,"Y")

  • For the months within a year

=DATEDIF($B5,$D$1,"YM")

  • For the days within 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 *

The DATEDIF function in Excel

Reading time: 2 minutes
Last Updated on 12/05/2024

The DATEDIF function in Excel calculates the difference between two dates. It often determines age, tenure, or periods between dates.

Calculating the difference between 2 dates is not easy in Excel. You must consider whether you want to return a result based on the gap of the months, 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 these 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

The DATEDIF function is hidden 🤨🤔

The previous example showed that calculating the difference between 2 dates is NEVER easy. But 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 calculations 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 functions, but it works perfectly (they're weird at Microsoft)

The DATEDIF function is not listed in the dialog box

The DATEDIF function in Excel

The DATEDIF function takes three arguments:

  1. Start_date

    This is the starting date of the period you want to calculate. This date should come before the End_date.

  2. End_date

    This is the ending date of the period. The End_date should be on or after the Start_date.

  3. Unit

    The Unit specifies the type of difference you want to calculate between the two dates

=DATEDIF(Start_date, End_date, Unit)

Results return by each parameter of the DATEDIF function

The 3rd argument of the function is the most important

The first 3 codes are easy to understand

  • "Y": Returns the number of complete years between the start and end dates.
  • "M": Returns the number of complete months between the dates.
  • "D": Returns the number of days between the dates

The other codes return a value within month or day limits

  • "MD": Returns the difference in days, ignoring months and years; results between 0 and 30
  • "YM" returns the difference in months, ignoring days and years; the result is between 0 and 11.
  • "YD": Returns the difference in days, ignoring years; results between 0 and 364

Calculate the seniority

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

Seniority of the employees calculated with DATEDIF

For each column, we have the following formulas

  • For the years

=DATEDIF($B5,$D$1,"Y")

  • For the months within a year

=DATEDIF($B5,$D$1,"YM")

  • For the days within 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 *