# Assigning a number to a date range

#### bon

##### Board Regular
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

### Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
Are you looking to find what quarter a certain date is in?

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

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

jimbojones said:
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

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

Tazguy37 said:
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

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

Would something like this work?:
test2.xls
ABCDEF
1StartDateEndDateQuarterDateQuarter
21/1/20044/4/200417/5/20043
34/5/20047/3/20042
47/4/200410/2/20043
510/3/200412/31/20044
Sheet1

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

Hope that helps!

jimbojones said:
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.

Tazguy37 said:
Would something like this work?:
test2.xls
ABCDEF
1StartDateEndDateQuarterDateQuarter
21/1/20044/4/200417/5/20043
34/5/20047/3/20042
47/4/200410/2/20043
510/3/200412/31/20044
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!!!

Replies
5
Views
150
Replies
5
Views
275
Replies
5
Views
360
Replies
7
Views
133
Replies
2
Views
102

1,203,608
Messages
6,056,286
Members
444,855
Latest member

### 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?

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