PDA

View Full Version : Solved: Recognizing quarters



Klartigue
10-21-2011, 10:01 AM
In column D, i have different dates in the short date term. In column F i would like to define which date that quarter goes into.

12/13 - 3/31 is Q1
3/31 - 6/31 is Q2
6/31 - 9/31 is Q3
9/31 - 12/31 is Q4.

Is there any way to include a code in my macro that will look at the date in column D and assign it the correct quarter it falls into?

Klartigue
10-21-2011, 10:02 AM
Annd it would be great if the code could do it for row 2 to lastrow

Paul_Hossler
10-21-2011, 12:18 PM
12/13 - 3/31 is Q1
3/31 - 6/31 is Q2
6/31 - 9/31 is Q3
9/31 - 12/31 is Q4.


When does Q1 start?

Worksheet formula or VBA?

Paul

Klartigue
10-21-2011, 12:20 PM
Q1 is January - March.
Q2 is April - June.
Q3 is July-September.
Q4 is October - December

Klartigue
10-21-2011, 12:21 PM
Right now, dates are just listed in column D and in colum F i would like to identify their respective quarter they fall in...using vba

GTO
10-21-2011, 01:18 PM
Hi there,

Try:
Option Explicit

Sub QuarterRetExplicit()
Dim wks As Worksheet

Set wks = ThisWorkbook.Worksheets("The tab name") '<---Change to suit, or better, just use
' CodeName like: Sheet1

With Range(wks.Range("D2"), wks.Cells(wks.Rows.Count, 4).End(xlUp))
.Offset(, 2).Value = _
Evaluate("CHOOSE(MONTH(" & wks.Name & "!" & .Address(0, 0) & ")," & _
"""Q1"",""Q1"",""Q1"",""Q2"",""Q2"",""Q2"",""Q3"",""Q3"",""Q3""," & _
"""Q4"",""Q4"",""Q4"")" _
)
End With
End Sub

mdmackillop
10-21-2011, 01:21 PM
Function Quarter(data)
Select Case Month(DateValue(data))
Case Is < 4
Quarter = 1
Case Is < 7
Quarter = 2
Case Is < 10
Quarter = 3
Case Else
Quarter = 4
End Select
End Function

jaydee
10-21-2011, 03:09 PM
was trying to figure this out myself (for practice learning). function works great thanks!

Paul_Hossler
10-21-2011, 04:24 PM
Building on the function approach a bit, I think it's a good idea to also add some simple error checking to catch some of the bad data and Option Explicit to the module to always required variable declarations



Option Explicit

Sub test()
MsgBox Quarter("7/2/2011")
MsgBox Quarter(#7/2/2011#)
MsgBox Quarter(CDate(49432.123))
If IsError(Quarter("asdf")) Then
MsgBox "Ooops"
Else
MsgBox Quarter("asdf")
End If
End Sub


Function Quarter(data As Variant) As Variant
If IsDate(data) Then
Select Case Month(data)
Case Is < 4
Quarter = 1
Case Is < 7
Quarter = 2
Case Is < 10
Quarter = 3
Case Else
Quarter = 4
End Select
Else
Quarter = CVErr(xlErrNA)
End If
End Function


Paul

xlfan2007
10-23-2011, 07:54 AM
excel formula can do this

If your Dates are in D

=CHOOSE(ROUNDUP(MONTH(D1)/3,0),"Q1","Q2","Q3","Q4")

Klartigue
10-24-2011, 07:41 AM
That excel formula works great! I am trying to incorporate it into my VBA macro. However, with the below it says there is an error: expected end of statement..do you see where the problem lies?

Sub Quarter()

Dim NumBlocks As Long
Dim LastRow As Long
Dim i As Long

Windows("Volume.xlsx").Activate

With ActiveSheet

LastRow = .Cells(.Rows.Count, "D").End(xlUp).Row
For i = 2 To LastRow

.Cells(i, "D").Offset(0, 8).Select
ActiveCell.FormulaR1C1 = _
"=CHOOSE(ROUNDUP(MONTH(D1)/3,0),"Q1","Q2","Q3","Q4")"
Next i
End With

End Sub

Klartigue
10-24-2011, 07:45 AM
Sub Quarter()

Dim NumBlocks As Long
Dim LastRow As Long
Dim i As Long

Windows("Volume.xlsx").Activate

With ActiveSheet

LastRow = .Cells(.Rows.Count, "D").End(xlUp).Row
For i = 2 To LastRow

.Cells(i, "D").Offset(0, 2).Select
ActiveCell.FormulaR1C1 = _
"=CHOOSE(ROUNDUP(MONTH(RC[-2])/3,0),""Q1"",""Q2"",""Q3"",""Q4"")"
Next i
End With

End Sub

Nevermind, I figured it out!!

Klartigue
10-24-2011, 07:53 AM
Now column D still has dates as 10/19/2011, 5/12/2011, etcc..

Is there a way to alter this =choose function so it looks at the date in column D and identifies the year. So a function that looks at D2, sees 10/19/2011, and places 2011 in another column?

Klartigue
10-24-2011, 08:13 AM
Sub Year()
Range("G2:G" & Range("D" & Rows.Count).End(xlUp).Row).Value = Range("D2:D" & Range("D" & Rows.Count).End(xlUp).Row).Value
Range("G2:G" & Range("G" & Rows.Count).End(xlUp).Row).NumberFormat = "[$-409]yyyy;@"

End Sub

Nevermind, I figured it out!!

mdmackillop
10-24-2011, 11:02 AM
Try to avoid Select. You can also set the Year value in the adjoining column

.Cells(i, "D").Offset(0, 2).FormulaR1C1 = _
"=CHOOSE(ROUNDUP(MONTH(RC[-2])/3,0),""Q1"",""Q2"",""Q3"",""Q4"")"

.Cells(i, "D").Offset(0, 3).FormulaR1C1 = "=YEAR(RC[-3])"