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 able to find this application but we still can 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 it’s three arguments we can find the difference between those two dates in days, months and years as well.
- After applying DATEDIF formula you are supposed to get the result in 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 an example let’s get the difference between two dates in years, for your better understanding, we will take the start date and end date similar in all examples. For an instance let’s assume Start Date as 21/01/2016 and End Date as 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 the answer 3 which means there is a difference of there 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 the answer in fraction 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 that in this formula we have to use the unit “m” instead of “y”.
- Rest of the formula will remain 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 calculation, 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 with 12.
- A formula to find the difference in days is =DATEDIF(start_date, end_date,”D”)
- We can see for 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 its showing the difference of date numerics.
- For 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 start date is of month #9 and 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 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 by 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 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 when you are calculating the years or months, it might not be the exact answer, as the system has to consider an average day for every month and years.
- 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 downloadable excel template. You can also go through our other suggested articles –
