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
2014 Q1
2014 Q1
2014 Q1
2014 Q2
2014 Q2
2014 Q2
2014 Q3
2014 Q3
2014 Q3
2014 Q4
2014 Q4
2014 Q4

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]01/01/14[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]01/02/14[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]01/03/14[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]01/04/14[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]01/05/14[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]01/06/14[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]01/07/14[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]01/08/14[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]01/09/14[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]01/10/14[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]01/11/14[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]01/12/14[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]B1[/TH]
[TD="align: left"]=YEAR(A1)&" Q"&ROUNDUP(MONTH(A1)/3,0)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]


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!