Creating a custom function for fiscal quarters

Vicky

New Member
Joined
Sep 17, 2002
Messages
7
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

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

gnaga

Well-known Member
Joined
Jul 9, 2002
Messages
700
Office Version
  1. 365
  2. 2016
  3. 2013
Platform
  1. Windows
Sorry for asking this. Where is the Fiscal Qtr worksheet function in Excel?

GNaga
 

Vicky

New Member
Joined
Sep 17, 2002
Messages
7
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

Well-known Member
Joined
Jul 9, 2002
Messages
700
Office Version
  1. 365
  2. 2016
  3. 2013
Platform
  1. Windows
Sorry I am not sure. May be someone else can help for you on this.

GNaga
 

Mark W.

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,654

ADVERTISEMENT

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.

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,654
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
 

Vicky

New Member
Joined
Sep 17, 2002
Messages
7

ADVERTISEMENT

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

Active Member
Joined
May 16, 2007
Messages
403
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

Active Member
Joined
May 16, 2007
Messages
403
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.
 

Forum statistics

Threads
1,144,330
Messages
5,723,746
Members
422,513
Latest member
Meathead2022

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top