return value if date is between two other dates, depending on the year of another cell

[danwoltrs]

Hey guys, I have a huge spreadsheet with data since 2010 and I want to see prices per period, depending on year and for when the goods were sold. I have two columns with date, one is the day of trade(column B) and the other is day the goods will be delivered (column C), then I have a third column with price (D) - next two columns are where the formulas will go, Column (E) will be for nearby delivery and column (D) will be for future deliveries. I got another spreadsheet with defined names so I won't get lost too easy, these defined names are basically the start month and end month to determine the nearby shipment and future shipment.

Here is what my defined names look like to determine the deliveries for nearby & future

cellname	From	to	cellname
start10	July, 1, 2010	June, 3, 2011	finish10
start11	July, 1, 2011	June, 3, 2012	finish11
start12	July, 1, 2012	June, 3, 2013	finish12
start13	July, 1, 2013	June, 3, 2014	finish13

B	C	D	E	F
Day of trade	Delivery	Price	Nearby delivery	Future delivery
January, 5, 2010	Feb, 2013	4	formula1, false NA()	formula2, True = D
September, 10, 2010	April, 2011	15	formula1, True = D	formula2, false NA()

I have the following formula1 so far:

Please Login or Register  to view this content.

Code:
=IF(YEAR([@Closing])=2010,IF(C2>finish10,[@Diff], NA()))
I can't seem to figure out a way to do this code work for my whole spreadsheet, I need something like this:

If(YEAR(Day of trade cell) is between start10 and finish10 AND (delivery month&year) is also between start10 and finish10, show price) IF (YEAR(Day of trade cell) is between start11 and finish11 AND (delivery month&year) is also between start11 and finish11, show price)) this repeated until 2016 // there are about 11k lines on this, which is why I wanted this formula to auto-determine.

My problem that I can't figure out how to make formulas 1 and 2:

Formula1 Column E

case 1: If day of trade (column B) January 2010, it checks if the delivery date (column is between start10(July 2009 ) and finish10(June 2011), if true then value of cell = price cell (column D), if false, NA()

case 2: if day of trade (column B) is October 2011, it checks if delivery date is a nearby delivery date: July 2011 (start11) and June 2012(finish12) then it has to see if the delivery date is between the same period, and so on... until today's date.

Formula2 Column F
If day of trade is for instance January 10, it has to see if the delivery date is bigger than finish10, if true then the value is price of cell, if not, NA()


Thanks in advance for any help or tips on this

[Pete_UK]

There is a lot to take in here, but it would help if you attached a sample Excel workbook to explain things a bit more clearly. To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post.

Pete

[danwoltrs]

Thanks Pete, I made here an example of the workbook, please ignore the 'chart' sheet, what I am trying to work on is on sheet database and variables.

I color coded the nearby and future cells with green where the value should go to, if you see on the first two cells, it works because the year of sales date corresponds to the same year it should (2010), but the others are just false because I can't get the formula to check if the year of sales is 2011 it should check start11 finish11, and so on...

Once again, thanks for replying

[Pete_UK]

I'll be going out soon, Dan, so it will be later on before I get chance to look at this.

Pete

[JohnTopley]

TRY

in F2

=IF(AND(C2 > VLOOKUP(YEAR([@Date]),Table4,2,0),C2 < VLOOKUP(YEAR([@Date]),Table4,3,0)),[@Price], NA())

in G2

=IF(C2 > VLOOKUP(YEAR([@Date]),Table4,3,0),[@Price], NA())

Extra space round " > " and " < " to get round firewall!

[danwoltrs]

Hey Pete and John, thanks for both replies

John thanks a lot for prompt reply, I tried your formula, and it works well for some cases, but in some cases it won't work, for instance:

If in December 2014 I have sold something for January 2015, it should still be considered as nearby, but since the year is higher, it shows on as future.

[JohnTopley]

case 1: If day of trade (column B) January 2010, it checks if the delivery date (column is between start10(July 2009 ) and finish10(June 2011), if true then value of cell = price cell (column D), if false, NA()

case 2: if day of trade (column B) is October 2011, it checks if delivery date is a nearby delivery date: July 2011 (start11) and June 2012(finish12) then it has to see if the delivery date is between the same period, and so on... until today's date.

My logic handles Case 1, as I understand it. If case 1. fails, should the NA() not be replaced by Case 2 check (which is iterative so I not sure how this is done)?

What is the result of case 2? the price?

And anything between July 09 to Jun 16 is a potential "nearby delivery date" ????

I'll leave it to the guru (Pete).

[danwoltrs]

Thanks again for helping me out John

My logic handles Case 1, as I understand it. If case 1. fails, should the NA() not be replaced by Case 2 check (which is iterative so I not sure how this is done)?

What is the result of case 2? the price?

And anything between July 09 to Jun 16 is a potential "nearby delivery date" ????

I'll leave it to the guru (Pete).

Result in case 2 is also the price, but since it is future, it goes on culumn G as you wrote before, it works on some cases -sorry if I can't make it very clear, will try and explain in further details:

Product I sell is actually coffee, and I want to separate by crop year. Coffee is harvested in July, and will be sold from July,YY through June'YY+1
Sometimes I can sell a coffee two years ahead, which is priced much cheaper than if I sell for next month - this is the study I am trying to make.

So if in October 2015 (crop year 15/16) I sell the same coffee, but different delivery period:
A) February 2016 (same crop year) at 2 USD/Lb (price should go on column F)
B) February 2017 (crop year 16/17) at 1.80 USD/Lb (price should go on column G)

I want to make a comparison of prices to boost my clients' interest to purchase ahead of time, that is why I am trying to do that

[danwoltrs]

Hi John,

I tried something different based on your logic of using vlookup, which I found very creative/clever! Now I extended my search on vlookup and created a new table called shipment where I basically have spread out all months and years from Jan 2010 till today, and next to it the begining of the crop and end of the crop. Now I tried to edit the VLOOKUP to find the same month and year instead of only year, this would solve the problem I think, but I can't seem to get it to work.

This is the formula I tried:

Please Login or Register  to view this content.

Can't seem to get it to work like that, do you know any trick?

[JohnTopley]

Try this ....

I added another column G to "Shipments" concatenating Year and Month

=YEAR(H2)&MONTH(H2)

Copied down

then used ..

=IF(AND(C2 > VLOOKUP(YEAR([@Date])&MONTH([@Date]),shipment,3,1),C2 < VLOOKUP(YEAR([@Date])&MONTH([@Date]),shipment,4,1)),[@Price], NA())

[danwoltrs]

Thanks John, it looks like it is skipping a few inputs, at first it looked okay, then I saw that some months were good and others not, check it out - painted in orange where the price actually should go.

I changed the start and end month to August - July

[JohnTopley]

Change to

=IF(AND(C3 > VLOOKUP(YEAR([@Date])&MONTH([@Date]),shipment,3,0),C3 < VLOOKUP(YEAR([@Date])&MONTH([@Date]),shipment,4,0)),[@Price], NA())


=IF(C2 > VLOOKUP(YEAR([@Date])&MONTH([@Date]),shipment,4,0),[@Price], NA())

[danwoltrs]

Awesome John!! Didn't even notice the 1 there before

it worked, thank you very much!! :D

[JohnTopley]

Mea Culpa! as I forgot to change it when I added the new lookup.

If you have a solution (thanks to you adding the table!) could you please mark the thread as solved ("thread Tools" at top of first post)