Dates in a Financial Year

[Kurt]

If I have a financial year August01-July31, is there a worksheet function(s)
which I can use to convert date such as 22/08/09 to read 'Qtr1, 2009' or
23/03/10 to read 'Qtr 3, 2009' etc. Any advice?

 

[Sam Wilson]

If you have a date in A1:

="Qtr"&INT((IF(MONTH(A1)<8,MONTH(A1)+5,MONTH(A1)-7)-1)/3)+1&" " &
IF(MONTH(A1)<8,YEAR(A1)-1,YEAR(A1))

 

[Jacob Skaria]

With your date in cell A1; try the below formula

="Qtr"&LOOKUP(--TEXT(A1,"mm"),{1,2,5,8,11},{2,3,4,1,2}) & TEXT(A1," yyyy")

If this post helps click Yes

 

[Sam Wilson]

LOOKUP(--TEXT(A1,"mm"),{1,2,5,8,11},{2,3,4,1,2}) is a much better way of
doing it than my way, but TEXT(A1,"yyyy") won't work as 23/03/10 is in
financial year 2009, so you'll need the if(month(A1)...) still.

 

[Ashish Mathur]

Hi,

Type this in range C9:C12 - 2,5,8,11. Type this in range D912 - Qtr 3,Qtr
4,Qtr 1,Qtr 2. Assuming your date in cell B15, in cell C15, you may use the
following formula

=VLOOKUP(MONTH(B15),$C$9:$D$12,2)&",
"&IF(MONTH(B15)<8,YEAR(B15)-1,YEAR(B15))

Hope this helps.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Jacob Skaria]

Yes you are right. I havent noticed that..

="Qtr "&LOOKUP(--TEXT(A1,"m"),{1,2,5,8,11},{2,3,4,1,2}) & " " &
YEAR(A1)-(--TEXT(A1,"m")<8)

If this post helps click Yes

 

[Bob Phillips]

="Qtr"&MAX(1,INT((MOD(MONTH(A1)+4,12)+3)/3))&","&YEAR(A1)-(MONTH(A1)<8)

 

[Rick Rothstein]

Roughly the same idea, but implemented in a slightly different way (one function call less)...

="Qtr "&(1+MOD(INT((MONTH(A4)+4)/3),4))&", "&(YEAR(A4)-(MONTH(A4)<8))

Just as an alert to the OP, your formula omitted a couple of "neatening" spaces (which can easily be added, of course) that the OP's examples showed he apparently wanted.

 

[Dave Peterson]

I use this formula to show the fiscal year and quarter:

="FY"&YEAR(A1)-(MONTH(A1)<#)&"-Q"&INT(1+MOD(MONTH(A1)-#,12)/3)

So if the fiscal year starts on Oct 1st, then I'd use:
="FY"&YEAR(A1)-(MONTH(A1)<10)&"-Q"&INT(1+MOD(MONTH(A1)-10,12)/3)

It results in an expression like:
FY2009-Q1

It makes sorting easier.