If dates are 33 days apart add

[tsakta13ole]

Hi. I'm attaching my workbook. I have a column with dates. For example,
Column A
07/02/2006
11/02/2006
21/04/2006
06/10/2006
01/03/2007
01/06/2007

I also have another column with dates (third Friday of each month).
Column B
20/01/2006
17/02/2006
17/03/2006
21/04/2006
19/05/2006
16/06/2006
21/07/2006
18/08/2006
15/09/2006
20/10/2006
17/11/2006
15/12/2006
19/01/2007
16/02/2007
16/03/2007
20/04/2007
18/05/2007
15/06/2007
20/07/2007
17/08/2007
21/09/2007

Whenever dates in column A are apart 33 days or more I want to add the dates in column B so that the dates apart in column A are less than 33. The new column has to include all the dates of column A. In this case column C has to be:
07/02/2006
11/02/2006
17/03/2006
21/04/2006
16/06/2006
21/07/2006
15/09/2006
06/10/2006
17/11/2006
19/01/2007
01/03/2007
20/04/2007
01/06/2007

Hope you understand what I'm trying to do!
Thanks!

P.S I have posted this problem in another forum http://www.mrexcel.com/forum/excel-q...ml#post4466025

[Crooza]

Sorry double posted - this site drives me crazy some days

see below

[Crooza]

Not sure I fully follow the logic. Why for example doesn't it pick up 17/2/06 and jump to 17/3/06 instead

anyway try this

In E1

=A1

In E2

=IF(INDEX($A$1:$A$6,MATCH(E1,$A$1:$A$6,1)+1)-E1<33,INDEX($A$1:$A$6,MATCH(E1,$A$1:$A$6,1)+1),INDEX($B$1:$B$21,MATCH(E1,$B$1:$B$21,1)+1))

and drag down column E

[tsakta13ole]

Thank you for your answer! It's not quite what I want but it's tough to explain.
Can your formula change so that when it takes values from column B they are not each month but every other month? I don't have a problem if dates from column A are a few days only apart but I want dates from column B to be every other month.

Thanks again.

[Crooza]

OK. try this but it still misses out on the 19/6/06. I'm still not clear on your logic to be homest

=IF(INDEX($A$1:$A$6,MATCH(E1,$A$1:$A$6,1)+1)-E1<33,INDEX($A$1:$A$6,MATCH(E1,$A$1:$A$6,1)+1),INDEX($B$1:$B$21,MATCH(EOMONTH(E1,0),$B$1:$B$21,1)+1))

[tsakta13ole]

Thank for your effort and interest!
I will try to explain my logic.

Column A includes dates that I would like to buy some stock options (the dates come from another excel). The stock options have specific expiry dates (third Friday of the month, column B). In this case, I want to check a system that buys options that will expire after 33 days or more (lookup 32 days column B). If events (column A) happen before the option expires I want to have the date of the next event and not the option.
For example, let's say I have an event on 01/01/2016. I enter into an option then with an expiry date on 19/02/2016. If an event happens before that (let's say 30/01/2016) this will be my "target" column:
01/01/2016
30/01/2016
Then I buy an option on 30/01/2016. If the next event is on 30/05/2016 (column A) the option will expire. So I need the expiry date which in this case will be 18/03/2016. On 18/03/2016 I will buy a new option that expires on 20/05/2016 which is before the next event. So the column I want will now be:
01/01/2016
30/01/2016
18/03/2016
20/05/2016
30/05/2016

Hope you understand what I'm trying to do!

[tsakta13ole]

Hi again. I'm rewriting my problem so it is in one post.
I attach a sample of my workbook.
Column A
07/02/2006
11/02/2006
20/04/2006
06/10/2006
21/10/2006
Column B
20/01/2006
17/02/2006
17/03/2006
21/04/2006
19/05/2006
16/06/2006
21/07/2006
18/08/2006
15/09/2006
20/10/2006
Column C
07/02/2006
11/02/2006
17/03/2006
20/04/2006
16/06/2006
18/08/2006
20/10/2006
21/10/2006
I'm looking for a formula or macro that will give me the results in column C.
Column A includes dates that I would like to buy some stock options (the dates come from another excel). The stock options have specific expiry dates (third Friday of the month, column B). In this case, I want to check a system that buys options that will expire after 32 days or more (lookup 32 days column B). If events (column A) happen before the option expires I want to have the date of the next event and not the option. Events in column A will always be included in column C.


For example, let's say I have an event on 07/02/2006. I enter into an option then with an expiry date on 17/03/2006 (lookup more than 32 days from 07/02/2006 in column B). If an event happens before that (as is the case here with 11/02/2006) I will sell and buy a new option on that date so I don't care anymore about 17/03/2006. Column C will now be:
07/02/2006
11/02/2006
Then I buy an option on 11/02/2006. The expiry date will be again 17/03/2006 (lookup more than 32 days from 11/02/2006 in column B). So the column I want will now be:
07/02/2006
11/02/2006
17/03/2006 because there is no event in column A after 11/02/2006 and before 17/03/2006.

Now, the next event is 20/04/2006. The expiry date will be 16/06/2006. Column C will now be
07/02/2006
11/02/2006
17/03/2006
20/04/2006
16/06/2006
Now, the next event is on 06/10/2006. I closed the option on 16/06/2006 (it expired). I buy a new option on 16/06/2006 with an expiry date on 18/08/2006. (32 days or more from column B). 32 days are used because I want the time to expiry to be at least 32 days.

Sorry for the large post.
Hope you understand what I'm trying to do.
Thanks for any help!

[Crooza]

This is a bit closer (in E2 and drag down) but when it gets to the March 2007 date's it's grabbing the wrong one.

=IF(INDEX($A$1:$A$6,MATCH(E1,$A$1:$A$6,1)+1)-E1<33,INDEX($A$1:$A$6,MATCH(E1,$A$1:$A$6,1)+1),INDEX($B$1:$B$21,MATCH(E1+33,$B$1:$B$21,1)+1))

I'll reread your explanation and try to figure it out