IF Statement for a date range???

andycreber

Board Regular
Joined
May 20, 2010
Messages
65
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
Joined
Aug 12, 2009
Messages
1,691
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
Joined
May 20, 2010
Messages
65
that did not work

Here is the data layed out better to understand


INVOICE_DATEStart End Cycle
01/04/201231/03/2012 06/04/2012 1
11/04/201207/04/2012 13/04/2012 2
20/04/201214/04/2012 20/04/2012 3
27/04/201221/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
Joined
Aug 12, 2009
Messages
1,691
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:

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

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
Top