You may know that Excel can subtract from each other not only numbers, but also dates. The fundamental difference between the two dates will be the number of days between them. But if we want to determine the precise number of months, years or just months indiscriminately years, it would be quite difficult. Fortunately, we have a hidden function DATEDIF. It is really a hidden, in the Function Wizard you will not find it. Read this manual how to use it and try to do the example below. If this guide has helped you, become a fan on Facebook and recommend this site to your friends, it can be useful for them too.
NOTE: You must enter DATEDIF to the cell through manual entry. After writing it is possible to activate the function wizard by fx button in the formula bar, but no help for the function is there.
=DATEDIF(date 1; date 1; interval)
- date 1 … date cell, which must be less than the date 2
- date 2 … date cell, which must be greater than the date 1
- interval … label identifying the type of period is to be calculated (days, months, etc.). See the table below
|m||Months||Complete calendar months between the dates.|
|d||Days||Number of days between the dates.|
|y||Years||Complete calendar years between the dates.|
|ym||Months Excluding Years||Complete calendar months between the dates as if they were of the same year.|
|yd||Days Excluding Years||Complete calendar days between the dates as if they were of the same year.|
|md||Days Excluding Years And Months||Complete calendar days between the dates as if they were of the same month and same year.|
Why is this a hidden feature? Who knows, but probably it remained in Excel for backward compatibility with older versions. And that may be the reason that the function can falsify the result of months or years, especially at the extremes of dates, which are on the border between months.
To understand the function look at the following example.
- Cell A1 … 10.2.2014
- Cell B1 … 10.2.2016
- =DATEDIF(A1;B1;”d”) … number of days between dates, result 730
- =DATEDIF(A1;B1;”m”) … number of months between dates, result 24
- =DATEDIF(A1;B1;”y”) … number of years between dates, result 2
- =DATEDIF(A1;B1;”ym”) … number of months between dates, as they would be in the same year, result 0
See more date functions here Useful date functions.