DATEDIF Function in Excel

• The Excel DATEDIF function takes the start date, end date, and unit and calculates the difference in terms of days, months, or years between these two dates.
• The unit specifies what you require; days, months, or years.

Syntax for using DATEDIF function

The general format for using the DATEDIF is:

DATEDIF(start_date,end_date,unit)

I will explain the arguments, especially units after a few examples so you may understand easily.

An example of Excel DATEDIF for calculating the 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 the B6 column to show the difference in days:

=DATEDIF(A2,B2,"D")

The result:

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 month difference between two dates

For this example, I have used dates as text strings in the DATEDIF function rather than referring from the cell. Two dates are given while “M” value is used for the unit argument and see the result:

The formula:

=DATEDIF("1/13/2017","6/25/2017","M")

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 birth.
• 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:

=DATEDIF(A2,TODAY(),"Y")

In B3:

=DATEDIF(A3,TODAY(),"Y")

For the 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 the start date is greater than the 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 the unit argument. Following is the complete list of values for the unit:

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, the 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:

=DATEDIF(A2,B2,"D")

The D3 formula:

=DATEDIF(A3,B3,"MD")

The result:

You can see that similar dates are used and how ignoring months and years has resulted.

An example of YM unit value

See the dates and output with “M” and “YM” values:

The formula with YM:

=DATEDIF(A3,B3,"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 the 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:

=DATEDIF(A3,B3,"YD")

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 Years, 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:

=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 with A3 and so on.

The B column displayed the age in years only (as in the previous example) whereas the C column calculated the age in Years, Months, and days.