Creating a custom function for fiscal quarters

[Vicky]

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

 

[gnaga]

Sorry for asking this. Where is the Fiscal Qtr worksheet function in Excel?

GNaga

 

[Vicky]

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?

 

[gnaga]

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

GNaga

 

[Mark W.]

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

How about...

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

...for any datevalue entered into cell A1.

 

[Mark W.]

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
	A	B	C	D	E	F	G	H	I	J
1	Date	Amount		SumofAmount
2	12/1/02	45		Date	Total
3	2/15/02	32		Qtr1	32
4	5/30/02	46		Qtr2	70
5	6/13/02	24		Qtr3	52
6	7/23/02	18		Qtr4	88
7	9/25/02	34		GrandTotal	242
8	11/21/02	43
9
10
11
Sheet1

 

[Vicky]

Mark,

The

"How about...

="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

 

[koolwaters]

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?

Thanks in advance.

 

[koolwaters]

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.