Hey, welcome back to the Mr. Excel net cast, I am Bill Jelen. Basics, we start out with massive amounts of data and see how we are going to analyze this. Well, let us fire up our activity table and see how we can solve this problem.
Hey and welcome back to the Mr. Excel netcast. I'm Bill Jelen. Todays question sent in by Med. Med is from Denmark. Med has begin date and an end date and he just want to figure out the number of years, months and days between them. He doesn’t want to use some average like 30 days per month and there is an exact function that will do this. The only problem is Microsoft won't tell you about it. It’s the one function that has been in excel and they’ve only documented it once, I think back in Excel 2000. It was never documented since then. It’s been around forever and probably because it was Lotus or something sort of compatibility.
So, here's how it works. The function is called Date Dif. The Date Dif function specify the earlier data 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, all right so that works out. I am going to put that up here in C1 and I will press F4 twice to lock that down to adjust the row. And so that is 29 years, cool alright. We’ll copy that over where we get to see them in years, everything is—but now the problem is when we copy this over to get month, look at this, 351 months, well what is that? That’s 29 years times 12 plus the months that are left over so it’s very unintuitive.
The code up here instead of M for a month is YM, so that says hey we’ll already using Y somewhere else, just give me the months in excess of the last year and so there that’s 29 years or three month, alright. I want to copy this over one more time for the days. And again, look at that that’s the total number of days so not D now following on the same pattern I can kind of see whoever created this and 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, three months, four days.
There you have it, the Date Dif function, give that a try. I want to thank you for stopping by, we’ll see you next time for another netcast from Mr. Excel.
Well, thanks for stopping by, we’ll see you next time for another netcast from Mr. Excel.
Transcription by:
Scribe4you Transcription Services