Excel: Find the Number of Years, Months, and Days Between Two Dates

Today is my birthday! Woohoo!

Have you ever wondered exactly how old you are in years, months, and days? Here's a little-known formula in Microsoft Excel that can tell you the number of years, months, and days between two dates.

This is the main function that I'm going to be using. It's called DATEDIF:

=DATEDIF(date1,date2,"interval")

Here are some important things you'll need to know about using this function:

1. For some reason, this function doesn't show up in the list of functions in Excel. It won't appear in "Help", and it's going to look like you're entering a function that doesn't exist. This might have the positive effect of making you feel like a pro when you're using it!
2. Make sure you enter the earlier date as date1 and the later date as date2, or you'll get an error.
3. Remember to put the quotation marks around the interval. The intervals you can use are as follows: "m" (number of months), "d" (number of days), "y" (number of years), "ym" (number of months excluding the number of years), "yd" (number of days excluding the years), and "md" (number of days excluding the number of years and months).

I'm going to use my own birthday and today's date as an example, but you can go ahead and substitute those dates for any two of your choosing. I've combined this formula with CONCATENATE (to add in some text) and TODAY (to return the current date).

=CONCATENATE(DATEDIF(DATE(1987,10,19),TODAY(),"y")," years, ",DATEDIF(DATE(1987,10,19),TODAY(),"ym")," months, ",DATEDIF(DATE(1987,10,19),TODAY(),"md")," days")

That looks crazy, doesn't it? But it's really awesome. If you enter this into a cell today (October 19, 2010), it will give you the following result:

23 years, 0 months, 0 days

To give you a more simple example, here's a formula that will calculate the number of months between March 1, 2007 and October 1, 2010:

=DATEDIF(DATE(2007,3,1),DATE(2010,10,1),"m")

You can also reference cells that include dates:

=DATEDIF(A4,B4,"y")

This formula comes in extremely handy for a number of situations. I've most recently used it in a spreadsheet that included a number of peoples' birthdays, and I needed to know their current ages. Using the TODAY function in combination with DATEDIF helps those ages stay up-to-date without having to constantly go in and readjust the numbers manually.

That's all I have for today! Please feel free to send me an e-mail if you can help me figure out an Excel function that will produce a piece of chocolate birthday cake. I'm still trying to figure that problem out...

Categories: Excel, formulas, functions, Microsoft Office 2007, Microsoft Office 2010