Need a formula to identify a certain time period

[copleyr]

Hello all,

In column "A", I have a list of dates. I need a Vlookup formula that will look at the date and If the time period falls in between 2/1/11 and 4/30/11, I need it to say "1Q", If the time period falls in between 5/1/11 and 7/31/11, I need it to say "2Q", and resectively for the rest of the year.

Can anybody help?

 

[jim may]

Excel Workbook
	A	B	C	D	E	F	G
1	My Dates	Q-Test			Start	End	Qtr
2	2/1/2011	1Q			1-Feb	30-Apr	1Q
3	2/16/2011	1Q			1-May	31-Jul	2Q
4	3/3/2011	1Q			1-Aug	31-Oct	3Q
5	3/18/2011	1Q			1-Nov	31-Jan	4Q
6	4/2/2011	1Q
7	4/17/2011	1Q
8	5/2/2011	2Q
9	5/17/2011	2Q
10	6/1/2011	2Q
11	6/16/2011	2Q
12	7/1/2011	2Q
13	7/16/2011	2Q
14	7/31/2011	2Q
15	8/15/2011	3Q
16	8/30/2011	3Q
17	9/14/2011	3Q
18	9/29/2011	3Q
19	10/14/2011	3Q
20	10/29/2011	3Q
21	11/13/2011	4Q
22	11/28/2011	4Q
23	12/13/2011	4Q
24	12/28/2011	4Q
25	1/12/2012	4Q
26	1/27/2012	4Q
27	2/11/2012	4Q
Sheet1

Excel 2007

Cell Formulas
Range	Formula
B2	=VLOOKUP(A2,$E$2:$G$5,3,1)
B3	=VLOOKUP(A3,$E$2:$G$5,3,1)
F2	=EOMONTH(E2,2)
F3	=EOMONTH(E3,2)
A3	=A2+15
E3	=F2+1

 

[Sahak]

Hi Copleyr,

Maby this?

=IF(AND(MONTH(B1)>=2,MONTH(B1)<=4),"1Q",IF(AND(MONTH(B1)>=5,MONTH(B1)<=7),"2Q",IF(AND(MONTH(B1)>=8,MONTH(B1)<=10),"3Q",IF(OR(MONTH(B20)=11,MONTH(B20)=12,MONTH(B20)=1),"4Q"))))

 

[barry houdini]

You could use LOOKUP like this

=LOOKUP(MONTH(A2),{1,2,5,8,11;4,1,2,3,4})&"Q"

where your date is in A2

 

[jim may]

I can't quite break-the-code of your formula. Can you explain? Thanks, Jim

 

[copleyr]

I ended up doing Jim's initial way. Thank you so much to all!