If your fiscal year does not end on December 31, then all of the cool date grouping functions available in pivot tables will not work for you. In Episode 1033, you will see how to add a couple of user-defined functions to your personal macro workbook to simplify the conversion of date to Fiscal Quarter or Fiscal Year.
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, we start out with massive amounts of data.
So, how we're gonna analyze this. Well, let's plus fire up a pivot table.
Let's see if you can solve this problem.
Hey Welcome back to the MrExcel netcast. I'm bill Jelen.
And today's question comes in from live why Bob is creating a pivot 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 whose fiscal year ends somewhere else Well, you know Microsoft really doesn't handle this very well and so why Babb had a horrible formula now you had to enter all the time to convert the daily date to fiscal quarter and up to fiscal year in the Original Data set he said is there some easier way to do this. Well? Yeah, there is an easier way to do it We can create a custom User-defined function and store it in your personal macro Workbook now. How do we do that?
Well first of all you need to make sure 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 restore 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 a personal macro workbook to be there, okay? Now. We want to go look at VBA so we press alt f 11 and Open up personal Xls and open up modules and so you see that we have on this case I have three different modules module one You'll always have a module one because of that thing you just recorded and you can actually get rid of that tiny little macro We just needed to force personal dot xls to be there now. Here's what we're going to do I'm going to 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 a date and then the month the year fiscal year-end so like for example if your fiscal year ends march 31st You'd put a three there as the second primary now for you You work for a company and the fiscal year is really hard coded so you could shorten this dramatically?
I try to make it generic so that 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 has a select case to parent returns values qtr one two three or four depending on the quarter if it's anything else Then we came up with ten here are returned the F5 function takes my date and a 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 workbooks that way It'll be available to any workbook that you ever opened on this computer. We can close visual basic here and go back let's just create a Simple Little 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 Fq the date and then the fiscal year ending let's say that our fiscal year ends at the end of september See we've got quarter three there if we put in a different date for example 1 15 2009 At his quarter 2 and then to get the fiscal year equal personal tod Xls exclamation point Fy and that date comma 9 It 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 they don't handle any fiscal year-end actually you could probably simplify it quite a bit if Your fiscal year end is of course fixed for the company that you work for Whether you have it great question from Lab app want to thank you for stopping by we'll see you next time for another net cast From Mr.. Excel Well, thanks for stopping bye.
We'll see you next time for another netcast from MrExcel.
I'm Bill Jelen.
Basically, we start out with massive amounts of data.
So, how we're gonna analyze this. Well, let's plus fire up a pivot table.
Let's see if you can solve this problem.
Hey Welcome back to the MrExcel netcast. I'm bill Jelen.
And today's question comes in from live why Bob is creating a pivot 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 whose fiscal year ends somewhere else Well, you know Microsoft really doesn't handle this very well and so why Babb had a horrible formula now you had to enter all the time to convert the daily date to fiscal quarter and up to fiscal year in the Original Data set he said is there some easier way to do this. Well? Yeah, there is an easier way to do it We can create a custom User-defined function and store it in your personal macro Workbook now. How do we do that?
Well first of all you need to make sure 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 restore 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 a personal macro workbook to be there, okay? Now. We want to go look at VBA so we press alt f 11 and Open up personal Xls and open up modules and so you see that we have on this case I have three different modules module one You'll always have a module one because of that thing you just recorded and you can actually get rid of that tiny little macro We just needed to force personal dot xls to be there now. Here's what we're going to do I'm going to 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 a date and then the month the year fiscal year-end so like for example if your fiscal year ends march 31st You'd put a three there as the second primary now for you You work for a company and the fiscal year is really hard coded so you could shorten this dramatically?
I try to make it generic so that 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 has a select case to parent returns values qtr one two three or four depending on the quarter if it's anything else Then we came up with ten here are returned the F5 function takes my date and a 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 workbooks that way It'll be available to any workbook that you ever opened on this computer. We can close visual basic here and go back let's just create a Simple Little 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 Fq the date and then the fiscal year ending let's say that our fiscal year ends at the end of september See we've got quarter three there if we put in a different date for example 1 15 2009 At his quarter 2 and then to get the fiscal year equal personal tod Xls exclamation point Fy and that date comma 9 It 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 they don't handle any fiscal year-end actually you could probably simplify it quite a bit if Your fiscal year end is of course fixed for the company that you work for Whether you have it great question from Lab app want to thank you for stopping by we'll see you next time for another net cast From Mr.. Excel Well, thanks for stopping bye.
We'll see you next time for another netcast from MrExcel.
