Lumping dates into quarters by year
 

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?

Lumping dates into quarters by year
 

try:

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

--
Gary''s Student - gsnu200835


"Anna Wood" wrote:

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?

Lumping dates into quarters by year
 

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 wrote:

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?

--

Dave Peterson

Lumping dates into quarters by year
 

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

"Gary''s Student" wrote:

try:

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

--
Gary''s Student - gsnu200835


"Anna Wood" wrote:

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?