Hey welcome back to the Mr. Excel netcast I am Bill Jelen. Basic, we start out with massive amounts of data and how are we going to analyze this? Well, let's fire up the activity table and see if we can solve this problem.
Hey, welcome back to the Mr. Excel netcast, I’m Bill Jelen. Today’s question comes in from Why Bob. Why Bob is creating activity table, he wants to take daily dates and roll them up to quarters and years. But that works great in a pivot table if your fiscal year ends on December 31st but for all the companies, their fiscal year ends somewhere else. Well, you know, Microsoft really doesn’t handle this very well. And so Why Bob had a horrible formula that he had to enter all the time to convert the daily date up to fiscal quarter and up to fiscal year and there so many data. And he said, is there some easier way to do this? Well, yeah, there is an easier way to do it, that we can create a custom user define function and store it in your personal macro workbook.
Now, how do we do that? Well, first of all, you need to make sure that there is a personal macro workbook. And if you’ve never used macros before, you may not have one, so go to the view tab, macros, record macro. And it’s really important that we’re going to store this in the personal macro workbook. This is the step to make sure that you have a personal macro workbook, so we’ll just click OK and do anything, type anything, press Enter, and then stop recording. That will force the personal macro workbook to be there.
Okay now, we want to go look at VVAs. We press Alt-F11 and open up personal.xls and open up modules. And you see that we have, on this case, I have three different modules. Module one, you’ll always have a module one because that thing you just recorded. And you can actually get rid of that tiny little macro. We just needed to force personal.xls to be there.
Now, here’s what we’re going to do. I already wrote the code here. You can copy the code from the screen. I’ll try and make it big, I wrote two custom macros. One of them called FQ for fiscal quarter. It takes the date and then the month that your fiscal year ends. So like for example, if your fiscal year ends March 31st, you’d put a three there as the second parameter.
Now for you, you work for a company and the fiscal year is really hard coded, so you could shorten this dramatically. I’ll try to make it generic so that it will work for anyone with any kind of a fiscal year end. Calculate what the month number is using the month function and then checks to see if the month number is less than the fiscal year end and then it has a select case in here and returns values QTR one, two, three, or four, depending on the quarter. If it’s anything else, and then we come up with an error, ERR, return.
The FY function takes my date and the fiscal year end, that’s pretty simple. We just figure out if the month of my date is less than or equal to the fiscal year end and then format appropriately. Now, we put this in the personal macro workbook so that way, it will be available to any workbook that you ever open on this computer. We can close visual basic here and go back. Let’s just create a simple one here. We’ll put in a date of 6/15/2009. And then when we want to use the FQ function, we have to use equal personal.xls, exclamation point, FY, the date and then the fiscal year. And I’ll say that our fiscal year ends at the end of September. See we got quarter three there. If we put in a different date, for example 1/15/2009, that is quarter two. And then to get the fiscal year, equal, personal.xls, exclamation point, FY, and that date, comma, nine, and returns 2008-2009.
So now, we have those functions, you don’t have to enter the big, huge if function all the time, and it will be available to all the workbooks that you open on this computer. Again, just a little bit of code here, this code is generic enough that it’ll handle any fiscal year and actually you could probably simplify it quite a bit if your fiscal year end is of course fixed for the company you work for.
Well, there you have it, great question from Why Bob. I wanna thank you for stopping by, we’ll see you next time for another netcast from Mr. Excel.
Transcription by:
Scribe4you Transcription Services