VBA: Identify dates in range belonging to latest 2 quarters

Rowland Hamilton

Active Member
Joined
Nov 13, 2009
Messages
250
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
 

Some videos you may like

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

Rowland Hamilton

Active Member
Joined
Nov 13, 2009
Messages
250
Here is a pretty good quarter identifier formula: =INT((MONTH(U9)-1)/3)+1, now just need to figure latest...
 

jim may

Well-known Member
Joined
Jul 4, 2004
Messages
7,484
Not sure I understand your request, but...
Excel Workbook
AB
36/1/2011Other
46/6/2011Other
56/11/2011Other
66/16/2011Other
76/21/2011Other
87/1/2011PQ
97/6/2011PQ
107/11/2011PQ
117/16/2011PQ
127/26/2011PQ
137/31/2011PQ
148/10/2011PQ
158/15/2011PQ
168/20/2011PQ
178/30/2011PQ
189/4/2011PQ
199/14/2011PQ
209/19/2011PQ
219/24/2011PQ
229/29/2011PQ
2310/4/2011CQ
2410/9/2011CQ
2510/14/2011CQ
2610/19/2011CQ
2710/31/2011CQ
2811/3/2011CQ
Sheet1
Excel 2007
Cell Formulas
RangeFormula
B3=IF(A3>=DATEVALUE("9/30/2011"),"CQ",IF(AND(A3"9/30/2011"),A3>DATEVALUE("6/30/2011")),"PQ","Other"))
 

Rowland Hamilton

Active Member
Joined
Nov 13, 2009
Messages
250
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:
Code:
Range("E2").Formula = "=MAX($A$2:$A$" & LastRow & ")"
Then use the quarter formula on the result and concatenate with year for
Code:
Range("F2").Formula = "=INT((MONTH(E2)+2)/3)"
Range("G2").Formula = "=YEAR(E2)&""Q""&F2"
Now need dynamic method for previous.
 
Last edited:

Rowland Hamilton

Active Member
Joined
Nov 13, 2009
Messages
250
And for Previous, could convert this Excel formula:
Code:
=IF(F2>1,YEAR(E2)&"Q"&F2-1,(YEAR(E2)-1)&"Q"&4)
to VBA speak:
Code:
Range("H2").Formula = "=IF(F2>1,YEAR(E2)&""Q""&F2-1,(YEAR(E2)-1)&""Q""&4)"
 

Watch MrExcel Video

Forum statistics

Threads
1,127,554
Messages
5,625,471
Members
416,108
Latest member
Ravenhold

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top