IF Statement for a date range???

[andycreber]

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

 

[Misca]

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]

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]

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.