Assigning a number to a date range

[bon]

I have a large list of bookings data, each row has a date that the order was booked. I would like to be able to add a column that assigns a number (quarter 1, 2, 3, or 4) to a date range. In other words, in the new column the formula would return a "1" for the dates 1-1-04 to 4-4-04 for quarter 1 and a "2" for the dates 4-5-04 to 7-3-04...and so on through Q4. I have tried adding a column (say column A) with an if statement that says if column B (the one populated with the date) is = to the range, then 1. I cannot get it to work. Thanks

 

[Tazguy37]

Are you looking to find what quarter a certain date is in?

=ROUNDUP(MONTH(A1)/3,0) will do it (date in A1).

 

[jimbojones]

Hello,

Your quarters end and begin on very strange dates so I think you will need a table showing quarter end dates. Let's say you have in D1:D4 the dates each quarter start then in E1:E4 you have the quarter number (i.e. 1,2,3,4)

Then in A1 you have your date and in A2 the following:
=VLOOKUP(A1,$D$1:$E$4,2)
That would return the quarter based on your table in D1:E4

Any use?

James

 

[bon]

Hello,

Your quarters end and begin on very strange dates so I think you will need a table showing quarter end dates. Let's say you have in D1:D4 the dates each quarter start then in E1:E4 you have the quarter number (i.e. 1,2,3,4)

Then in A1 you have your date and in A2 the following:
=VLOOKUP(A1,$D$1:$E$4,2)
That would return the quarter based on your table in D1:E4

Any use?

James

Tried vlookup but getting #N/A returned in the A2 column. I setup a table with beginning date of each quarter and the associated quarter. Not sure how the actual date is being evaluated

 

[jimbojones]

Hi,

Are you sure VLOOKUP is entered exactly as above (you don't want the false argument appended to the end of the formula otherwise won't work.. However, you can use the True arguement at the end of the formula.

If that is all set up correctly you will need to make sure dates are entered as dates and not text.

James

 

[bon]

Are you looking to find what quarter a certain date is in?

=ROUNDUP(MONTH(A1)/3,0) will do it (date in A1).

works well if the dates of the quarter are pure calender. Mine unfortunately have odd start and end dates

 

[jimbojones]

aving odd dates is why you need a table with the cut-offs and the vlookup formula with true (or omitted) last statement.

 

[Tazguy37]

Would something like this work?:

test2.xls
	A	B	C	D	E	F
1	StartDate	EndDate	Quarter		Date	Quarter
2	1/1/2004	4/4/2004	1		7/5/2004	3
3	4/5/2004	7/3/2004	2
4	7/4/2004	10/2/2004	3
5	10/3/2004	12/31/2004	4
Sheet1

Formula in F2 is:
=VLOOKUP(E2,$A$2:$C$5,3,1)

Hope that helps!

 

[bon]

Hi,

Are you sure VLOOKUP is entered exactly as above (you don't want the false argument appended to the end of the formula otherwise won't work.. However, you can use the True arguement at the end of the formula.

If that is all set up correctly you will need to make sure dates are entered as dates and not text.

James

Hello James,
OK, that was a big help and it worked. Thanks!!! You know why I got the bad return? When I entered the starting date of each quarter I failed to add the year so it automatically went in as '05. My data is for 2004, hence the problem. I am new, what do you mean about the false argument? Thanks much, have a great weekend.

 

[bon]

Would something like this work?:

test2.xls
	A	B	C	D	E	F
1	StartDate	EndDate	Quarter		Date	Quarter
2	1/1/2004	4/4/2004	1		7/5/2004	3
3	4/5/2004	7/3/2004	2
4	7/4/2004	10/2/2004	3
5	10/3/2004	12/31/2004	4
Sheet1

Formula in F2 is:
=VLOOKUP(E2,$A$2:$C$5,3,1)

Hope that helps!

Hello,

Yes vlookup is the answer, that works well also. Thanks much. Just a question, I can see how the f2 formula is taking the e2 date and searching the table. But what do the 3,1 refer to?

Thanks again!!!