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:
- Start_date
This is the starting date of the period you want to calculate. This date should come before the
End_date. - End_date
This is the ending date of the period. The
End_dateshould be on or after theStart_date. - Unit
The
Unitspecifies 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")
Mahbub
Oliver Street
31/12/2020 @ 18:12
How can i sum 1 year 9 month 29 days with 1year 5 month 5 days
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)