Assign quarter dynamically based on the starting month

[Shweta]

Hello All,

I have a data validation list for all the months from January to December. I want to assign quarters to my months based on the selection in the validation list.

For example, If "November (11)" is selected in the validation list, Q1 should be May-July and Q2 should be Aug-Oct. And for rest of the period, I want month itself.

[TABLE="width: 128"]
<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl65, width: 64"]Month[/TD]
[TD="class: xl65, width: 64"]Quarter[/TD]
[/TR]
[TR]
[TD="class: xl66"]1[/TD]
[TD="class: xl66"]q1[/TD]
[/TR]
[TR]
[TD="class: xl66"]2[/TD]
[TD="class: xl66"]q2[/TD]
[/TR]
[TR]
[TD="class: xl66"]3[/TD]
[TD="class: xl66"]q2[/TD]
[/TR]
[TR]
[TD="class: xl66"]4[/TD]
[TD="class: xl66"]q2[/TD]
[/TR]
[TR]
[TD="class: xl66"]5[/TD]
[TD="class: xl66"]may[/TD]
[/TR]
[TR]
[TD="class: xl66"]6[/TD]
[TD="class: xl66"]jun[/TD]
[/TR]
[TR]
[TD="class: xl66"]7[/TD]
[TD="class: xl66"]jul[/TD]
[/TR]
[TR]
[TD="class: xl66"]8[/TD]
[TD="class: xl66"]aug[/TD]
[/TR]
[TR]
[TD="class: xl66"]9[/TD]
[TD="class: xl66"]sep[/TD]
[/TR]
[TR]
[TD="class: xl66"]10[/TD]
[TD="class: xl66"]oct[/TD]
[/TR]
[TR]
[TD="class: xl66"]11[/TD]
[TD="class: xl66"]q1[/TD]
[/TR]
[TR]
[TD="class: xl66"]12[/TD]
[TD="class: xl66"]q1[/TD]
[/TR]
</tbody>[/TABLE]

Above is the table for what exactly I want.

Please help with an appropriate formula or vba code.

Thanks in advance!

Regards,
Shweta Jain

 

[DRSteele]

No doubt someone more clever can make a much better formula, but at least this one will get you on your way.

In A2 is the Data Validation cell that uses the field Month (i.e., A6:A17) as its List of allowable values. Choose a month in A2 and the table will populate with "q1" for that month and the next two, "q2" for the subsequent three months, and the month's name for the remainder. Copy A6 and C6 downwards to populate the cells.

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]

Book1
	A	B	C
1
2	Nov	11
3
4
5	Month	Month#	Quarter
6	Jan	1	q1
7	Feb	2	q2
8	Mar	3	q2
9	Apr	4	q2
10	May	5	May
11	Jun	6	Jun
12	Jul	7	Jul
13	Aug	8	Aug
14	Sep	9	Sep
15	Oct	10	Oct
16	Nov	11	q1
17	Dec	12	q1
Sheet46

Cell Formulas
Range	Formula
B2	=MONTH(DATEVALUE(A2&" 1"))
A6	=TEXT(B6*29,"MMM")
C6	=IF(OR(B6=IF($B$2+{0;1;2}>12,$B$2+{0;1;2}-12,$B$2+{0;1;2})),"q1",IF(OR(B6=IF($B$2+{3;4;5}>12,$B$2+{3;4;5}-12,$B$2+{3;4;5})),"q2",A6))

[/FONT]