ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Group date into Year and Quarters (https://www.excelbanter.com/excel-programming/342463-group-date-into-year-quarters.html)

Ctech[_21_]
Group date into Year and Quarters
 

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


--
Ctech
------------------------------------------------------------------------
Ctech's Profile: http://www.excelforum.com/member.php...o&userid=27745
View this thread: http://www.excelforum.com/showthread...hreadid=475100

Ron de Bruin
Group date into Year and Quarters
 
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"))

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Ctech" wrote in message
...

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


--
Ctech
------------------------------------------------------------------------
Ctech's Profile: http://www.excelforum.com/member.php...o&userid=27745
View this thread: http://www.excelforum.com/showthread...hreadid=475100

Jim Thomlinson[_4_]
Group date into Year and Quarters
 
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.
--
HTH...

Jim Thomlinson


"Ctech" wrote:


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


--
Ctech
------------------------------------------------------------------------
Ctech's Profile: http://www.excelforum.com/member.php...o&userid=27745
View this thread: http://www.excelforum.com/showthread...hreadid=475100

[email protected]
Group date into Year and Quarters
 
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!


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


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"

Vacation's Over
Group date into Year and Quarters
 
format yyyy q

"Ctech" wrote:


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


--
Ctech
------------------------------------------------------------------------
Ctech's Profile: http://www.excelforum.com/member.php...o&userid=27745
View this thread: http://www.excelforum.com/showthread...hreadid=475100


All times are GMT +1. The time now is 07:26 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com