Med asks how to calculate Years, Months, Days between two dates. Episode 1113 discusses the elusive DATEDIF function.
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Basically, you start out with massive amounts of data, and say “How we're going to analyze this?” Well, let's fire up a Pivot table and see if we can solve this problem!
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Today's question sent in by Med.
Med’s from Denmark, Med has a begin date and an end date, he has to figure out that number of years, months and days between them.
Doesn't want to use some average like 30 days per month, and there is an exact function that will do this.
I promise Microsoft won't tell you about it, it's the one function that's been in Excel, and they've only document at once, I think, back in Excel 2000, was never documented since then.
It's been around forever, and probably because it was, you know, Lotus or something, sort of compatibility.
So here's how it works, the function is called DATEDIF, the DATEDIF function.
You specify the earlier date, I'll press F4 F4 F4 to lock that down to the column, and then the later date, F4 F4 F4 to lock that down, alright.
Now here's the thing, we have to put in a code now, this third parameter is a code, and the code to get years, appropriately enough, is a Y, alright.
So that works out, I'm going to put that up here in C1, I'll press F4 twice to lock that down to just the row, and so that is 29 years, cool, right?
Copy that over, and we get to see how many years everything is.
But now the problem is, when we copy this over to get months, look at this, 351 months.
Well, what is that?
That's 29 years *12+ the months that are left over, so it's very unintuitive.
The code up here, instead of M for months, is YM.
So that says “Hey, we're already using Y somewhere else, just give me the months in excess of the last year.” And so there, that's 29 years 3 months, alright.
Now we're going to copy this over one more time for the days, alright and again, look at that, that's the total number of days, so not D. Now following the same pattern, I can kind of see whoever created this, what they're thinking, put MD!
The M says “We're using M somewhere else, so give me the days in excess of that month.” And so there we have 29 years 3 months 4 days, there you have it, the DATEDIF function, give that a try!
Want to thank you for stopping by, we'll see you next time for another netcast from MrExcel!
Well thanks for stopping by, we'll see you next time for another netcast from MrExcel!
Basically, you start out with massive amounts of data, and say “How we're going to analyze this?” Well, let's fire up a Pivot table and see if we can solve this problem!
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Today's question sent in by Med.
Med’s from Denmark, Med has a begin date and an end date, he has to figure out that number of years, months and days between them.
Doesn't want to use some average like 30 days per month, and there is an exact function that will do this.
I promise Microsoft won't tell you about it, it's the one function that's been in Excel, and they've only document at once, I think, back in Excel 2000, was never documented since then.
It's been around forever, and probably because it was, you know, Lotus or something, sort of compatibility.
So here's how it works, the function is called DATEDIF, the DATEDIF function.
You specify the earlier date, I'll press F4 F4 F4 to lock that down to the column, and then the later date, F4 F4 F4 to lock that down, alright.
Now here's the thing, we have to put in a code now, this third parameter is a code, and the code to get years, appropriately enough, is a Y, alright.
So that works out, I'm going to put that up here in C1, I'll press F4 twice to lock that down to just the row, and so that is 29 years, cool, right?
Copy that over, and we get to see how many years everything is.
But now the problem is, when we copy this over to get months, look at this, 351 months.
Well, what is that?
That's 29 years *12+ the months that are left over, so it's very unintuitive.
The code up here, instead of M for months, is YM.
So that says “Hey, we're already using Y somewhere else, just give me the months in excess of the last year.” And so there, that's 29 years 3 months, alright.
Now we're going to copy this over one more time for the days, alright and again, look at that, that's the total number of days, so not D. Now following the same pattern, I can kind of see whoever created this, what they're thinking, put MD!
The M says “We're using M somewhere else, so give me the days in excess of that month.” And so there we have 29 years 3 months 4 days, there you have it, the DATEDIF function, give that a try!
Want to thank you for stopping by, we'll see you next time for another netcast from MrExcel!
Well thanks for stopping by, we'll see you next time for another netcast from MrExcel!
