| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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" |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Lumping dates into quarters by year | Excel Worksheet Functions | |||
| How should I get the # of quarters in a year. | Excel Worksheet Functions | |||
| Calculating quarters of a year. | Excel Worksheet Functions | |||
| year quarters === Financial Year | Excel Discussion (Misc queries) | |||
| how do I convert a dates in a year quarters in a year? | Excel Discussion (Misc queries) | |||
![Ctech[_21_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
