Updated June 9, 2023

DATEDIF in Excel

The datedif function in Excel counts the days, months, and years between the two dates. DatedIf function was available in the older version of MS Office until 2007. We cannot find this application, but we can still use this function if we know the syntax. To use the Datedif function, we should have a start and end date and select what we want to count. Use D for days, M for months, Y for years, MD for months ignoring Days, and YD for days ignoring years.

Popular Course in this category

MS Excel Course Bundle - 36 Courses in 1 | 13 Mock Tests

Unit	Result
“y”	A difference in complete years.
“m”	A difference in a complete month.
“d”	A difference in complete days.
“md”	A difference in complete days, Ignoring months and years.
“ym”	A difference in complete months, Ignoring days and years.
“yd”	A difference in complete days, Ignoring years.

As if we have two different dates, by Excel DATEDIF formula and its three arguments, we can also find the difference between those dates in days, months, and years.
After applying the DATEDIF formula, you are supposed to get the result in a number, representing the difference between the dates in Excel.

How to Use the DATEDIF Function in Excel?

The DATEDIF function in Excel is widely used for different purposes; here, we take some examples.
We know many tools for calculating your age, but Excel and this formula are really fun.
In many areas of corporate, we can use it to identify the aging of a particular file/ report/ case; honestly, I am using it very much in my MIS to know the aging of the claims, so I can decide my priorities and give the attention to the oldest.

You can download this DATEDIF Function Excel Template here – DATEDIF Function Excel Template

Example #1

Find Yearly Differences

“y”	A difference in complete years.

For example, let’s get the difference between two dates in years; we will take the start date and end date similar in all examples for your better understanding. For instance, let’s assume the Start Date is 21/01/2016 and the End Date is 29/07/2019.

To Find out the difference between two dates in years in Excel, just follow the below steps:

Step 1 – Mention the start date and end date in the date format (Note: The date format can be changed from cell formatting); here, we have formatted the date in dd/mm/yyyy format, which is most common nowadays.

Step 2 – Now, in a separate cell, mention the Excel DATEDIF formula, which is =DATEDIF(start_date, end_date, unit)

Step 3 – Here, for this example, for the start date, select the rows accordingly, and for a unit in the formula, enter “y” (Note: Units always has to be mentioned in between double inverted comma); in this formula,” y “stands for years.

You can see that you got to answer 3, meaning there is a difference in their years between the start and end dates.

Example #2

Find the Difference in Months

“m”	A difference in a complete month

Now we have to find the difference. You can probably say that if there are 3 years, then it should be around 36 months, true, but as this formula doesn’t provide us with the answer infraction, so we just need to find it by another formula which has “m” (Month) instead of years.

As you can see from the images above, we have to use the unit “m” instead of “y” in this formula.
The rest of the formula will remain the same =DATEDIF(start_date, end_date,”m”)

For our example, you can see in the image that there are 42 months of gap between the start and end dates.

Example #3

Find Difference in Days

“d”	A difference in complete days

Using the same formula with different units, we can calculate the difference in days between these two dates.

The difference in days is very useful, as you’re unable to get the fraction values using this formula, so when it comes to relatively smaller calculations, you need the most accurate data available.
With this formula, you can calculate the difference in days and then divide it by 30, so you can get the difference in months, most probably, and then into years by dividing it by 12.
A formula to find the difference in days is =DATEDIF(start_date, end_date,”D”)

We can see from our given example there is a difference of 1285 days.

Example #4

Unit “md.”

“md”	A difference in complete days, Ignoring months and years.

Unit “md” can be useful when you want to count only days between the given dates, irrespective of months and years. Formula for this unit is =DATEDIF(start_date, end_date,”md”)
As per this unit, the dates will behave like they have been in the same month and year, Exactly like it’s showing the difference in date numerics.

For the given example, it provides the difference between days is 8. As we have learned before, in this function with this unit, only the dates will perform as numbers, so the difference between 29 and 21 we got is 8.

Example #5

Unit “ym.”

“ym”	A difference in complete months, Ignoring days and years.

From this unit, we can identify the difference in months between a start date and an end date, irrespective of the days and years. Formula for this unit is =DATEDIF(start_date, end_date,”ym”)

So the given example shows the answer 6, as the difference between month # 7 and month # 1 is 6.

Now, if the start date is month #9 and the end date is month #1, as the answer, this formula will show 4 as it will calculate that for the month from month # 9, it will take 4 months to reach month # 1.
So this is the logic used behind this formula or unit.

Example #6

Unit “yd.”

“yd”	A difference in complete days, Ignoring years.

From this particular function, you can find the difference in days between given dates, irrespective of years; it may count the months but only till the very next year, and it always provides the result in counting days.
Formula for this unit is =DATEDIF(start_date, end_date,”yd”)

For the given an example, it shows the answer 190.

As per the given image, you can see that the unit “yd” counts the difference from 21/07 to 29/01 for the given example.

So far, we have learned about six units for DATEDIF Function in Excel. As a conclusion for all the above examples, we can understand that to find the most accurate data. We should find the difference in days; for moderately accurate data, find the difference in months, and when the smaller difference of even months doesn’t bother our calculation try the difference in years.

Things to Remember about DATEDIF Function in Excel

Here we have to remember that it might not be the exact answer when calculating the years or months, as the system has to consider an average day for every month and year.
As we know, all months do not have the same number of days. It might affect your calculation when you are calculating for very long spans, so it should get a difference of about 1 month.

Quiz Result
Total Questions	Correct Answers	Wrong Answers	Percentage

DATEDIF in Excel