Change date to quarter

[karleajensar1]

I'm looking for an efficient way to change a column of dates to a corresponding quarter.

The column looks like:
<TABLE style="WIDTH: 53pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=71 border=0 x:str><COLGROUP><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 2596" width=71><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 53pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right width=71 height=17 x:num="39000">
10/10/2008
</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num="39000">1/10/2006</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num="39000">7/10/2007</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num="39000">5/10/2006</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num="39000">6/10/2008</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num="39000">4/10/2006</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num="39000">11/10/2007
</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num="39000">3/10/2006</TD></TR></TBODY></TABLE>

I need it change to:
2008-Q4
2006-Q1
2007-Q3
2006-Q2
2008-Q2
2006-Q2
2007-Q4
2006-Q1

I tried Find/Replace but when I search for example 1/*/2006 and replace it with "2006-Q1", not only are January dates replaced but also November.

I'm not committed to using the "2006-Q1" format I just need someway to signify the year and quarter. Thank you very much for your help.

Joe

 

[VoG]

Try

=YEAR(A1) & "-"&LOOKUP(MONTH(A1),{1,4,7,10},{"Q1","Q2","Q3","Q4"})

 

[onlyadrafter]

Hello,

try

=YEAR(A1)&IF(MONTH(A1)<4,"-Q1",IF(MONTH(A1)<7,"-Q2",IF(MONTH(A1)<10,"-Q3","-Q4")))

 

[Mike Blackman]

How about;

=YEAR(A1)&"-Q"&LOOKUP(MONTH(A1),{1,1;4,2;7,3;10,4})

EDIT: Beaten to it

 

[Scott Huish]

=year(a1)&"-Q"&-int(-month(a1)/3)

 

[phiero21]

Try the following:

Column A contains the dates u want to convert into quarters


Column B : =YEAR(A1)
COlumn C: =IF(MONTH(A1)<4,"Q1",IF(MONTH(A1)<7,"Q2",IF(MONTH(A1)<10,"Q3","Q4")))
Column D: =B1&"-"&C1


You can change the appearecnec of your result in column D

Hope it helps

 

[Datsmart]

Also:

=YEAR(A1)&" - Q"&INT((MONTH(A1)+2)/3)

Looks like everybody had a Quarter formula...

 

[karleajensar1]

Works perfect. You folks are amazing. Thanks!

 

[Hsibbs]

I just used this too!!! thnx!!!