You have two dates in Excel and want to find the elapsed years and months. The solution is to use an ancient function from the days of Lotus 1-2-3. Episode 357 shows you how to use the function, as well as a cool formatting trick to show years and months in the same cell.
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
Transcript of the video:
Hey, welcome back to the MrExcel podcast, I'm Bill, Jelen.
Here's a great question sent in by a viewer, we have dates in column A, and today's date in column B. Now in column B, you could actually use the =TODAY function, but what we want to do is we want to try and figure out how much time has elapsed between the two dates, both in years and months.
There's a great old function that's still in Excel, that goes back to the Lotus 1-2-3 days, it's called the DATEDIF function.
And basically, you specify the earlier date and then the later date, and then you tell what units you want.
So, for example, if we want to see years, put a “Y”, and it will tell us that 34 years have elapsed between those two dates.
Now, there's also a setting for M, for months, and D for days, which will tell us the absolute number of months, but what we really want here is the number of months, ignoring those 34 years.
So =DATEDIF, again put in the earlier date and then the later date, and finally, the code to put months ignoring the years is “YM”.
There's also similar codes for “MD”, which is the days ignoring the months and years, or “YD”, which is the days ignoring years.
Enter this and I'll tell us that it's been 34 years and 1 month, copy that down, we can very quickly see how many years and months.
On the actual question, they wanted one formula that would show years and months, and they wanted it shown as a fraction, this is kind of cool.
I put a formula in here that basically takes the years number and the months number divided by 12, so C2+D2/12, and here's the trick.
I'm going to select those cells and go to Format Cells, choose the Fraction tab which doesn't have exactly what I want, so we'll go to Custom and say that we want to /12.
And basically, then we'll get a nice number like 36 and 11/12, that means 36 years 11 months.
Great way to show the elapsed time between two dates using the ancient DATEDIF function.
Hey, thanks for stopping by, we'll see you next time for another podcast from MrExcel!
Here's a great question sent in by a viewer, we have dates in column A, and today's date in column B. Now in column B, you could actually use the =TODAY function, but what we want to do is we want to try and figure out how much time has elapsed between the two dates, both in years and months.
There's a great old function that's still in Excel, that goes back to the Lotus 1-2-3 days, it's called the DATEDIF function.
And basically, you specify the earlier date and then the later date, and then you tell what units you want.
So, for example, if we want to see years, put a “Y”, and it will tell us that 34 years have elapsed between those two dates.
Now, there's also a setting for M, for months, and D for days, which will tell us the absolute number of months, but what we really want here is the number of months, ignoring those 34 years.
So =DATEDIF, again put in the earlier date and then the later date, and finally, the code to put months ignoring the years is “YM”.
There's also similar codes for “MD”, which is the days ignoring the months and years, or “YD”, which is the days ignoring years.
Enter this and I'll tell us that it's been 34 years and 1 month, copy that down, we can very quickly see how many years and months.
On the actual question, they wanted one formula that would show years and months, and they wanted it shown as a fraction, this is kind of cool.
I put a formula in here that basically takes the years number and the months number divided by 12, so C2+D2/12, and here's the trick.
I'm going to select those cells and go to Format Cells, choose the Fraction tab which doesn't have exactly what I want, so we'll go to Custom and say that we want to /12.
And basically, then we'll get a nice number like 36 and 11/12, that means 36 years 11 months.
Great way to show the elapsed time between two dates using the ancient DATEDIF function.
Hey, thanks for stopping by, we'll see you next time for another podcast from MrExcel!
