# The DATEDIF function in Excel

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

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

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

## Frédéric LE GUEN

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

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

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

# The DATEDIF function in Excel

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

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

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

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

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

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