Group date into Year and Quarters

[Ctech]

Hi

Depending on the date i.e. 05/12/05, I want to write in the column next
to it the quarter its in i.e. 2005 Q4

It needs to be quick as I have 24k rows in my sheet. Thanks

Here is my code, however it sorts it just into 2004 Q1 or 2003 Q4.

Code:

Sub GroupInQTR()
Dim Cell As Range

Columns("D").Select
Application.CutCopyMode = False
Selection.Copy
Columns("E:E").Select
Selection.Insert Shift:=xlToRight

Range("E1").Select
ActiveCell.FormulaR1C1 = "QTR"

Range("E2").Select

Do

If IsEmpty(ActiveCell) = False Then

' 2003

If ActiveCell >= "01/10/2003" And ActiveCell <= "31/12/2003"
Then
ActiveCell.FormulaR1C1 = "2003 Q4"

' 2004

ElseIf ActiveCell >= "01/01/2004" And ActiveCell <=
"31/03/2004" Then
ActiveCell.FormulaR1C1 = "2004 Q1"

ElseIf ActiveCell >= "01/04/2004" And ActiveCell <=
"30/06/2004" Then
ActiveCell.FormulaR1C1 = "2004 Q2"

ElseIf ActiveCell >= "01/07/2004" And ActiveCell <=
"30/09/2004" Then
ActiveCell.FormulaR1C1 = "2004 Q3"

ElseIf ActiveCell >= "01/10/2004" And ActiveCell <=
"31/12/2004" Then
ActiveCell.FormulaR1C1 = "2004 Q4"


' 2005

ElseIf ActiveCell >= "01/01/2005" And ActiveCell <=
"31/03/2005" Then
ActiveCell.FormulaR1C1 = "2005 Q1"

ElseIf ActiveCell >= "01/04/2005" And ActiveCell <=
"30/06/2005" Then
ActiveCell.FormulaR1C1 = "2005 Q2"

ElseIf ActiveCell >= "01/07/2005" And ActiveCell <=
"30/09/2005" Then
ActiveCell.FormulaR1C1 = "2005 Q3"

ElseIf ActiveCell >= "01/10/2005" And ActiveCell <=
"31/12/2005" Then
ActiveCell.FormulaR1C1 = "2005 Q4"

' 2006

ElseIf ActiveCell >= "01/01/2006" And ActiveCell <=
"31/03/2006" Then
ActiveCell.FormulaR1C1 = "2004 Q1"

ElseIf ActiveCell >= "01/04/2006" And ActiveCell <=
"30/06/2006" Then
ActiveCell.FormulaR1C1 = "2004 Q2"

ElseIf ActiveCell >= "01/07/2006" And ActiveCell <=
"30/09/2006" Then
ActiveCell.FormulaR1C1 = "2004 Q3"

ElseIf ActiveCell >= "01/10/2006" And ActiveCell <=
"31/12/2006" Then
ActiveCell.FormulaR1C1 = "2004 Q4"

' For rest of the dates

ElseIf ActiveCell < "01/10/2003" Or ActiveCell > "31/12/2006"
Then
ActiveCell.FormulaR1C1 = ""


End If

ElseIf IsEmpty(ActiveCell) = True Then GoTo MissKaka

End If

ActiveCell.Offset(1, 0).Select

Loop

MissKaka:
Range("A1").Select
MsgBox "All the period cells are now updates to a 'Quarter
Format'"

End Sub

 

[Ron de Bruin]

Hi Ctech

DataRefiner have a option to insert a formula for you
http://www.rondebruin.nl/datarefiner.htm

Try this with the date in A2 and a start month of Jan

=--(YEAR(A2)&TEXT(IF(ROUNDUP((MONTH(A2)+(12-1+1))/3,0)>4,ROUNDUP((MONTH(A2)+(12-1+1))/3,0)-4,ROUNDUP((MONTH(A2)+(12-1+1))/3,0)),"00"))

 

[Guest]

Depending on what exactly you are trying to do you can use a pivot table. In
a pivot table you can group dates by month, quarter, year... Add the date
dimension, and right click on it. Select group and follow the wizard.

 

[jayj0nes]

I think you need to surround your dates in VBA with the pound sign (#).
For example...

If ActiveCell >= #10/1/2003# and ActiveCell <= #12/31/2003#

Also, notice my date format is m/d/yyyy where yours is d/m/yyyy. Excel
forces me to use m/d/yyyy format, but this may be due to my system's
date settings.

good luck!

 

[Guest]

format yyyy q