Determine fiscal quarter based on date

jaguar5554
jaguar5554 used Ask the Experts™
on
I use the following expression in an MS Access Query to return the fiscal year for a given date: FYear: Year([Date])-IIf([Date]<DateSerial(Year([Date]),7,1),1,0). (Fiscal Year starts 7/1 and ends 6/30).
I am requesting assistance in writing an expression that will return the fiscal quarter for a given date. (i.e. 3/1/09 = 3Q2008).
Thank you in advance!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2010

Commented:
Try this...

FYear: Format(DateAdd("m", -6, #6/30/2009#), "q""Q""yyyy")
jaguar5554Business Analyst

Author

Commented:
/thank you for the prompt response; unfortunately, the formula provided incorrectly returns the year 2008 to all dates (i.e 8/30/2007 = 1Q2007 and the formula returns 4Q2008)), and I do not believe it is calculating the quarter. Since the fiscal year starts 7/1, I believe the quarter should calculate as follows:
July Aug Sept = 1Q
Oct Nov Dec = 2Q
Jan Feb Mar = 3Q
Apr May Jun = 4Q

Again, any assistance is greatly appreciated! Thank you in advance...

Top Expert 2010
Commented:
My apologies.

FYear: Format(DateAdd("m", -6, [Date]), "q""Q""yyyy")
jaguar5554Business Analyst

Author

Commented:
Perfect! Thank you so very much for your prompt and expert response.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial