Splitting data in yearly quarters

[juleshsol]

Hi,

I have a large data set which include a date on each line. This ranges back to 2009. I need a formula which can identify which quarter each line fits into ie: date is 1/12/2011 - should read 'Q4 2011'

I have come up with a long winded formula of:

=YEAR(K173)&IF(MONTH(K173)<=3,"Q1",IF(AND(MONTH(K173)>3,MONTH(K173)<=6),"Q2",IF(AND(MONTH(K173)>6,MONTH(K173)<=9),"Q3","Q4")))

But is there a more efficient formula I could use?

Thank you

 

[Comfy]

Excel 2010

	A	B
1	01/01/14	2014 Q1
2	01/02/14	2014 Q1
3	01/03/14	2014 Q1
4	01/04/14	2014 Q2
5	01/05/14	2014 Q2
6	01/06/14	2014 Q2
7	01/07/14	2014 Q3
8	01/08/14	2014 Q3
9	01/09/14	2014 Q3
10	01/10/14	2014 Q4
11	01/11/14	2014 Q4
12	01/12/14	2014 Q4

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula B1 =YEAR(A1)&" Q"&ROUNDUP(MONTH(A1)/3,0) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Misread your question, updated:

Excel 2010
	A	B
1	01/01/14	Q1 2014
2	01/02/14	Q1 2014
3	01/03/14	Q1 2014
4	01/04/14	Q2 2014
5	01/05/14	Q2 2014
6	01/06/14	Q2 2014
7	01/07/14	Q3 2014
8	01/08/14	Q3 2014
9	01/09/14	Q3 2014
10	01/10/14	Q4 2014
11	01/11/14	Q4 2014
12	01/12/14	Q4 2014
Sheet1

Cell Formulas
Range	Formula
B1	="Q"&ROUNDUP(MONTH(A1)/3,0)&" "&YEAR(A1)

 

[juleshsol]

That is brilliant. Thank you so much!