Lumping dates into quarters by year

[Anna Wood]

I have a list of dates extending out for the next 8 years and I need to put
them in the quarter/year category - i.e., 3Q09 for a date that is 10/12/09.

Is there a formula to convert dates to this type of format?

 

[Gary''s Student]

try:

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

 

[Dave Peterson]

I like this formula in general--if the fiscal year starts on the first of month
number #:
="FY"&YEAR(A1)-(MONTH(A1)<#)&"-Q"&INT(1+MOD(MONTH(A1)-#,12)/3)

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

I also like this style of result:
FY2009-Q1

It makes sorting by that column easier.

 

[Anna Wood]

Thank you - it worked perfectly. I would have never gotten there on my own.