# Creating a custom function for fiscal quarters

#### Vicky

##### New Member
The fiscal qtr function in excel does not match my company's fiscal quarters. I believe I need to create a user-defined function to match our fiscal quarters.

Q1 = Dec 1st - Feb 29/28
Q2 = Mar 1st - May 31
Q3 = June 1st - Aug 31st
Q4 = Sep 1st - November 30th

Any help on how to define the function is much appreciated.

Thanks

### Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
Sorry for asking this. Where is the Fiscal Qtr worksheet function in Excel?

GNaga

My message was a bit misleading re custom function. I know that it's possible to pull out quarters based on a user defined function like:

Function Qtr&(dte As Date)

Qtr& = Format(dte, "q")

End Function

But, this assumes that the year begins on Jan 1st and ends on Dec 30th (calendar year).

How would I rewrite this so that it works if my financial year starts on December 1st?

Sorry I am not sure. May be someone else can help for you on this.

GNaga

On 2002-09-18 23:35, Vicky wrote:
...I believe I need to create a user-defined function to match our fiscal quarters.

Q1 = Dec 1st - Feb 29/28
Q2 = Mar 1st - May 31
Q3 = June 1st - Aug 31st
Q4 = Sep 1st - November 30th

Any help on how to define the function is much appreciated.

Thanks

="Q"&CHOOSE(MONTH(A1),1,1,2,2,2,3,3,3,4,4,4,1)

...for any datevalue entered into cell A1.

On 2002-09-19 01:30, gnaga wrote:
Sorry for asking this. Where is the Fiscal Qtr worksheet function in Excel?

GNaga

You can group dates into quarters within a PivotTable...
Book1
ABCDEFGHIJ
1DateAmountSumofAmount
212/1/0245DateTotal
32/15/0232Qtr132
45/30/0246Qtr270
56/13/0224Qtr352
67/23/0218Qtr488
79/25/0234GrandTotal242
811/21/0243
9
10
11
Sheet1

Mark,

The

="Q"&CHOOSE(MONTH(A1),1,1,2,2,2,3,3,3,4,4,4,1)

...for any datevalue entered into cell A1."

works a treat. Thanks so much.. such a simple solution for a problem that's been bugging me for ages..

Vicky

Hi Mark W,

How would this function work if my quarters are:

Nov 1 to Jan 31
Feb 1 to Apr 30
May 1 to Jul 31
Aug 1 to Oct 31

Can you also explain the function please?

I guess I was too tired last night but now that I've looked at the function this morning, I understand it totally.

Thanks for posting it.

Replies
4
Views
293
Replies
4
Views
579
Replies
7
Views
2K
Replies
8
Views
473
Replies
5
Views
369

1,220,987
Messages
6,157,239
Members
451,407
Latest member
vdaesety

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back