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.

Function DATEDIF

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
IntervalMeaningDescription
mMonthsComplete calendar months between the dates.
dDaysNumber of days between the dates.
yYearsComplete calendar years between the dates.
ymMonths Excluding YearsComplete calendar months between the dates as if they were of the same year.
ydDays Excluding YearsComplete calendar days between the dates as if they were of the same year.
mdDays Excluding Years And MonthsComplete 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.

Example

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
Funkce Datedif

Funkce Datedif

Similar posts about this topic

Leave a Reply

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