Excel DATEDIF Function (Table of Contents)
DATEDIF in Excel
Datedif function in Excel is used to count the number of days, months, and years between the two dates. DatedIf function used to be available in the older version of MS Office till 2007. We will not be able to 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 date, end date and then 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.
| 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 find the difference between those two dates in days, months, and years as well.
- After applying the DATEDIF formula, you are supposed to get the result in a number, which represents 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 of the examples.
- To calculate your age, we know that there are many tools for these calculations, but from Excel and this formula is the real fun.
- In many areas of corporate, we can use it to identify the aging of the 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.
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 as of 21/01/2016 and the End Date as of 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 format of the date 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 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, which means there is a difference in their years between the start date and end date.
Example #2
Find Difference in Months
| “m” | A difference in a complete month |
- Now we have to find the difference in probably you can 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”)
- Here for our example, you can see in the image that there are 42 months of gap between the start date and end date.
Example #3
Find Difference in Days
| “d” | A difference in complete days |
By 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 not able 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 you can 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 units, the dates will behave like it’s been in the same month and the same year, Exactly like it’s showing the difference of date numerics.
- For the given example, it provides the difference between days is 8, as we have learned before that 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 that from the difference in months between a start date and end date irrespective of the days and years. Formula for this unit is =DATEDIF(start_date, end_date,”ym”)
- So for the given example, it shows the answer 6, as the difference between month # 7 and month # 1 is 6.
- Now, if the start date is of month #9 and the end date is of month #1, as the answer, this formula will show 4 as it will calculate that for the month that 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 you with the result in counting days.
- Formula for this unit is =DATEDIF(start_date, end_date,”yd”)
- For the given example, it shows the answer 190.
- As per the given image, you can see that the unit “yd,” it’s counting 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 don’t bother our calculation try the difference in years.
Things to Remember about DATEDIF Function in Excel
- Here we have to keep in mind that it might not be the exact answer when you are 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 the difference of about 1 month.
Recommended Articles
This has been a guide to DATEDIF in Excel. Here we discuss DATEDIF Formula and how to use the DATEDIF Function in Excel along with practical examples and a downloadable excel template. You can also go through our other suggested articles –
