How to determine the difference in 2 dates in days, months or years using a function that is not available in the help or function wizard.
- For updated video clips in structured Excel courses with practical example files, have a look at our MS Excel online training courses . You can even try the Free MS Excel tips and tricks course.
- To see if this video matches your skill level (see the suggested skill score below) do our free MS Excel skills assessment.
- If you are based in South Africa look at the live courses we offer in Johannesburg and Cape Town.
- - 03:35
- - Skill Level: 7
- - What's my skill level
Date Difference Excel function
The DateDif function is unique in that you cannot find it in the Help files or the Function Wizard and it is one of the date difference excel functions you can use.
If you want to use this function, you are going to have to learn the syntax on your own. Basically the syntax is DateDif, Start date, End date and Type. What this allows you to do is to specify start and end date, determine what you want the answers – you can have it in days, or the number of months between two dates or the number of years or the number of days assuming it was in the same year, the number of months assuming it was in the same year or the number of days assuming it was in the same month. The easiest way to explain this is with an example. Over here we have already done a calculation but let’s just repeat it – say equals datedif, specify the first date, the second date. You’ll see here for the type I’m going to actually enter it, if you watch over here, you need to put inverted commas round it – so you’ll see it tells us there are 494 days between the 1st Jan 09 and the 10th May 10. I am now going to do the same, now we are going to ask for the months. Equals datedif, start date, end date, I’m now going to say please tell me the number of months, I close it and when I push enter, you’ll see it tells me there are 16 months between the 1st Jan and the 10th May. Now let’s do it based on the years. So we are going to say – equals datedif, start date, end date and the type now is year in inverted commas, close it, you’ll see it is one year between that date and this date. Using the “yd” indicator, you’ll see there are 129 days- that’s because it ignores the 2010 year and basically says what happens if it were 10th May 2009 – it gives you the number of days and the same here, using “ym” as our indicator, you’ll see it tells us 4 months- again its because its left out the 2010 and just assumed its 2009, so from January to May its 4 months and “md” , start date, end date, type, and you’ll see it says 9 days and that’s because it again ignores the year and the month and now it says from the 1st to the 10th is 9 days