# IF Statement for a date range???

#### andycreber

##### Board Regular
Hi

I want to write a formula to return a value when the source is between date ranges. The data I have is below:

source data calendar return value
Invoice Date date range to be
04/04/2012 (A1) 31/03/2012 - 06/04/2012 (B1) w/e 1
07/04/2012 (A2) 07/04/2012 - 13/04/2012 (B2) w/e 2

If invoice date is between then date range then return the relevent w/e 1 or 2

Thanks

Andy

### Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

#### Misca

##### Well-known Member
You should have a column with the first day of each date range. That way you're comparing dates with dates, not dates with text as you are right now. Once you can compare dates to dates the rest is really straightforward and you can use VLOOKUP or INDEX/MATCH to get what you need.

If you're only interested in week numbers starting from 31/03/2012 I'd try to getaway without the lookup functions and just play with the number of full weeks between the starting date and given date:

="w/e "&INT((A2-\$E\$2)/7)+1

The starting date is located in E2 in my example.

#### andycreber

##### Board Regular
that did not work

Here is the data layed out better to understand

 INVOICE_DATE Start End Cycle 01/04/2012 31/03/2012 06/04/2012 1 11/04/2012 07/04/2012 13/04/2012 2 20/04/2012 14/04/2012 20/04/2012 3 27/04/2012 21/04/2012 27/04/2012 4

<TBODY>
</TBODY><COLGROUP><COL span=2><COL span=2><COL span=2><COL></COLGROUP>

So what I want to achieve is: Where the invoice date is within the start and end range for that row, then return the cycle number

IE: Invoice date = 11/04/2012 return 2
IE: Invoice date = 27/04/2012 return 4

many thanks

You should have a column with the first day of each date range. That way you're comparing dates with dates, not dates with text as you are right now. Once you can compare dates to dates the rest is really straightforward and you can use VLOOKUP or INDEX/MATCH to get what you need.

If you're only interested in week numbers starting from 31/03/2012 I'd try to getaway without the lookup functions and just play with the number of full weeks between the starting date and given date:

="w/e "&INT((A2-\$E\$2)/7)+1

The starting date is located in E2 in my example.

#### Misca

##### Well-known Member
Say you have your lookup data in a named range called "Data" (the normal way works just as well but I like to use named ranges whenever I can for they make formulas much easier to understand and work with. For a simple tutorial about named ranges go to http://www.contextures.com/xlNames01.html). Also, make sure you have your lookup data sorted in ascending order (= Start dates -column) and your table has the same three columns as your sample data (Start, End and Cycle).

Now try a simple VLOOKUP (if you're not familiar with VLOOKUP take a look at http://www.contextures.com/xlFunctions02.html):

=VLOOKUP(A2,Data,3)

The formula looks for the values in the first column of your Data-range and once it finds a value greater than what it's looking for it steps one step back (=previous row) and shows the value from the 3rd column.

Last edited:

Replies
4
Views
105
Replies
16
Views
488
Replies
10
Views
429
Replies
5
Views
161
Replies
6
Views
267

1,130,118
Messages
5,640,211
Members
417,131
Latest member
Seanr19871

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

### Which adblocker are you using?

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

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