Fiscal Quarter UDF - 1033 - Learn Excel from MrExcel Podcast - YouTube

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.