VBA: Identify dates in range belonging to latest 2 quarters

[Rowland Hamilton]

Folks:
With VBA in Excel, how can I Identify dates in a range as belonging to the latest 2 quarters in the range and label them in an adjacent column as CQ (Current Quarter), PQ (Prior Quarter) and Other (for dates outside of the last 2 quarters).

Attached is a sample result file. IDLastest2Quarters.zip
Thank you, Rowland

 

[Rowland Hamilton]

Here is a pretty good quarter identifier formula: =INT((MONTH(U9)-1)/3)+1, now just need to figure latest...

 

[Rowland Hamilton]

Simplified formula = =INT((MONTH(A2)+2)/3)

 

[jim may]

Not sure I understand your request, but...

Excel Workbook
	A	B
3	6/1/2011	Other
4	6/6/2011	Other
5	6/11/2011	Other
6	6/16/2011	Other
7	6/21/2011	Other
8	7/1/2011	PQ
9	7/6/2011	PQ
10	7/11/2011	PQ
11	7/16/2011	PQ
12	7/26/2011	PQ
13	7/31/2011	PQ
14	8/10/2011	PQ
15	8/15/2011	PQ
16	8/20/2011	PQ
17	8/30/2011	PQ
18	9/4/2011	PQ
19	9/14/2011	PQ
20	9/19/2011	PQ
21	9/24/2011	PQ
22	9/29/2011	PQ
23	10/4/2011	CQ
24	10/9/2011	CQ
25	10/14/2011	CQ
26	10/19/2011	CQ
27	10/31/2011	CQ
28	11/3/2011	CQ
Sheet1

Excel 2007

Cell Formulas
Range	Formula
B3	=IF(A3>=DATEVALUE("9/30/2011"),"CQ",IF(AND(A3"9/30/2011"),A3>DATEVALUE("6/30/2011")),"PQ","Other"))

 

[Rowland Hamilton]

Sorry, data is dynamic so I won't know what dates the user will select. Could be an as of Jan 2010 report or could be run in June of 2012...

Possible way to identify most recent quarter:

Range("E2").Formula = "=MAX($A$2:$A$" & LastRow & ")"

Then use the quarter formula on the result and concatenate with year for

Range("F2").Formula = "=INT((MONTH(E2)+2)/3)"
Range("G2").Formula = "=YEAR(E2)&""Q""&F2"

Now need dynamic method for previous.

 

[Rowland Hamilton]

And for Previous, could convert this Excel formula:

=IF(F2>1,YEAR(E2)&"Q"&F2-1,(YEAR(E2)-1)&"Q"&4)

to VBA speak:

Range("H2").Formula = "=IF(F2>1,YEAR(E2)&""Q""&F2-1,(YEAR(E2)-1)&""Q""&4)"