How To Calculate Periods Between Dates Using DATEDIF In Excel

In this tutorial we are going to look at the undocumented function in Excel DATEDIF. This could be a great tool to dynamically calculate the number of years, months and days between two dates.

You can download the file here and follow along. If you get a preview, look for the download arrow in the upper right hand corner.

Excel has an undocumented function called DATEDIF. If you start to type =DATEDIF in Excel it will not present the explanation or the syntax, but this formula allows you to calculate the period between two dates in years, months or days, including or excluding various components.

The syntax for this function is:

=DATEDIF(start_date, end_date, unit)

The unit is the unit of measure that you want the results to appear in. There are six different units, here are the first three:

Y – years
M – months
D – days

These three just calculate the full periods of years, months or days between two dates. So, here are the results of the formulas using these units with the following dates:

7516-1

The other three units are:

YM – full months, ignoring days and years
YD – days, ignoring years
MD – days, ignoring months and years

Here are the results of the DATEDIF function using these three units:

7516-2

So, “YM” takes the months only – December (12) minus July (7) and gives the result of 5.
“YD” takes the days, ignoring the years and subtracts them. December 31 minus July 12 of the same year would be 172 days.
“MD” takes the days, ignoring the years AND months and subtracts them. The 31 minus 12 equals 19.

Now, what if you wanted to create a dynamic calculation between two dates that would be something like this:

14 years, 5 months, and 19 days

All you would need to do is concatenate three of the DATEDIF functions with some text as follows:

=DATEDIF(C1,C2,”Y”)&” years, “&DATEDIF(C1,C2,”YM”)&” months, and “&DATEDIF(C1,C2,”MD”)&” days”

You can see that we used the DATEDIF function with a “Y” for the years, with a “YM” to get the months ignoring the days and years, and finally with an “MD” to get the days, ignoring the months and years. We concatenated in between those the text to indicate years, months and days. Now when we change any of the dates, Excel will dynamically adjust the results of the period between them:

7516-3

What can you do next?
Share this post with others that can benefit from it!
Leave a comment or reply below – let me know what you think!
Subscribe to this blog for more great tips in the future!
Check out my YouTube channel – click on the YouTube icon below!

Happy Excelling!

Related Post

Free Download!

Subscribe to Download Your FREE Copy of
"My 70+ Favorite Excel Keyboard Shortcuts" Today!

, , ,

5 Responses to How To Calculate Periods Between Dates Using DATEDIF In Excel

  1. M. Saleh May 2, 2017 at 7:19 am #

    Dear Michael,
    I have used below function in my excel file and to find an age of students:
    =DATEDIF(A2,A1,”y”)&” Years, “& DATEDIF(A2,A1,”ym”)&” Months, “&DATEDIF(A2,A1,”MD”)&” Days.”
    where A1 = 2017/09/10 –> 10th Sep.
    and A2= 2010/10/12 –> 12th Oct.

    I have noticed that there are different results (one day exactly) when I use this formula in different PCs. One pc shows (6 Years, 10 Months, 29 Days.) and the other shows (6 Years, 10 Months, 28 Days.) for the same inputs and same file.

    Could you help me please.

  2. Eric May 2, 2017 at 9:35 am #

    There was a datediff bug in XL2007
    Which version is used?

  3. M. Saleh May 3, 2017 at 3:44 am #

    Dear Micheal and Eric,

    Thanks for your response. Kindly note that I’m using XL2007 on windows 7. I have tried this function on many pc and all have shown the same result except one, which show a result differ by one day only.

    I have notice one think on that PC which maybe helpful to my problem, now I’m using a date of 10th Sep. 2017 (which is the start date of new academic year) to calculate the student age, when the student birthday is less than 10 (ex: 1st Nov. 2010) there is no problem with the result, but when the student birthday is greater than 10 (ex: 12th Nov. 2010) the is a problem with result.

    Your comments and cooperation will be highly appreciated.

Leave a Reply