Solved: Recognizing quarters

[Klartigue]

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]

Annd it would be great if the code could do it for row 2 to lastrow

[Paul_Hossler]

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]

Q1 is January - March.
Q2 is April - June.
Q3 is July-September.
Q4 is October - December

[Klartigue]

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]

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]

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]

was trying to figure this out myself (for practice learning). function works great thanks!

[Paul_Hossler]

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]

excel formula can do this

If your Dates are in D

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

[Klartigue]

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]

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]

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]

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]

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])"