The Excel DATEDIF function takes the start date, end date, unit and calculates the difference in terms of days, months or years between these two dates. The unit specifies what you require; days, month, or years.
Syntax for using DATEDIF function
The general format of using the DATEDIF is:
I will explain the arguments, especially units after a few examples so you may understand easily.
An example of Excel DATEDIF for calculating number of days
Let us start with a simple example of using DATEDIF function to calculate the number of days between two dates. Suppose, we have a “Purchase Date” column and another is “Sale Date”. We want to see the difference in terms of days.
The following formula is used in B6 column to show the difference of days:
You can see, the difference between “5/22/2015” and “6/13/2017” is 22 days. The “D” argument specified that we require the number of days.
An example of getting Months difference between two dates
For this example, I have used dates as text strings in the DATEDIF function rather referring from the cell. Two dates are given while “M” value is used for the unit argument and see the result:
So, the number of Months between 1/13/2017 and 6/25/2017 is 5. Generally, the cell values are referenced in the formulas. The purpose of the example is just to show that the dates as text strings can also be used.
The example of calculating Years difference by DATEDIF
The DATEDIF Excel function can be used for calculating the age. In this example, we will calculate the number of years between two dates:
- Column A contains the Date of Borth.
- Column B shows the age.
- The age is calculated based on the DOB column. In that case, the TODAY() function served the second date in the DATEDIF function.
The following formula is used in cell B2:
For B column, the respective A column is used in the formula till B6. See the result:
So, the DATEDIF returned the number of years between two dates: Birth date and Today. You might notice, the “Y” value is used for the unit argument to get the number of years.
What if start date is greater than end date?
The start date argument must be less than the end date. If the start date is greater than the end date then #REF! error occurs. See an example below:
Exploring the type argument in DATEDIF
Until now, we used three different values in the DATEDIF function for unit argument. Following is the complete list of values for the unit:
- “D” – Specifies the number of days.
- “M” – Number of complete months
- “Y” – Number of complete years
- “MD” used to get the difference between the days while months and years are ignored. The official statement suggests avoiding using MD function due to known limitations. It may result in 0, negative number or inaccurate result.
- “YM” – Difference between the months while days/years of the dates are ignored.
- “YD” – The difference between the days of start and end dates while years of the dates are ignored.
Now let me show you a few examples of using the MD, YM and YD values and how these are useful.
The example of MD in DATEDIFF
To understand the difference between “D” and “MD” values, have a look at the following example. As such, the “MD” ignores the months and years in the given dates, I have used same dates for A2 and A3 columns. Also, same dates are used for B2 and B3 columns.
The D2 and D3 are assigned the DATEDIF formula; D2 with “D” unit while D3 with “MD” unit value. See the difference yourself:
The D2 formula:
The D3 formula:
You can see, similar dates are used and how ignoring months and year has resulted.
An example of YM unit value
See the dates and output with “M” and “YM” values:
The formula with YM:
You see, using YM resulted in 2 for the same dates because it ignored the days and years in dates.
The YD example in DATEDIF
The YD ignores the years in Start and end dates and gets the difference of days between two dates. For the demo, again I used dates with Year difference and compared “YD” with “D” to show how it works:
The DATEDIF formula with YD in D3 cell:
From the result, it is evident that the date is calculated by ignoring the year in the case of YD. Basically, dates are taken as follows:
3/25 and 6/7 i.e. number of days between 25 March and 7 June are returned.
A practical example of YM and MD in DATEDIF
You may wonder what can be the usage of YM and MD unit values? Let me show this by a practical example by extending the above Age example that was based on Birth Date and TODAY() date.
It calculated the age and displayed the years only. What if you want to calculate the age that includes Year, Months and Days? This is where you may apply the YM and MD unit values as shown in the formula and resultant sheet:
The formula used in C2 cell:
1 =DATEDIF(A2,TODAY(),"y") &" Years and "&DATEDIF(A2,TODAY(),"YM") &" Month(s) and C3" &DATEDIF(A2,TODAY(),"MD") &" Days"
In the C3 column, I just replaced the A2 by A3 and so on.
The B column displayed the age in years only (as in the previous example) whereas C column calculated the age in Year, Months and days.