Function regarding difference in the days

[pao13]

Hi.
I attached my spreadsheet.

In column F (time) I want to compare column P and column B and when the cell in column P is over 16 days ahead of the cell in column B, the cell in column F to be the difference in the days between the two. An example so you understand what I want to do.
P33 is 17/01/2014 and B33 is 02/01/2014. The difference in the days is under 16. When this happens I want F33 to be (P34-B33)/365. If the difference was 16 days and over I want F33 to be (P33-B33)/365.

Hope you understand what I want to do.

 

[eibi]

Does this do it?

=IF(P33-B33<16,(P34-B33)/365,(P33-B33)/365)

All best.

 

[pao13]

This works but the problem is in the next rows of this column. The even numbers of the column F34, F36 etc will have a different formula so I can hide them. If you see in the excel If I drag the fill handle the formula will stop working at some point.
I attached the updated excel. I have hide even rows because they will have a different formula. Column P has the date of the third Friday of every month.
My english is not my native language so sorry for any errors.

 

[eibi]

Pao,

I observe the first error on the same row as the first blank in Column P. Is this your concern?

 

[pao13]

No. Only F33 is correct. In F35 I want to compare B35 (January 17th) with the data in column P and pick the cell in column P that is over 16 days but under -lets say- 50 days. So there is only one price in column P to compare.

Hope you understand what I'm trying to do.

 

[eibi]

I do think I see -- but one more question -- when you hid the even rows, you also hid half of the expiration dates in column P. Do you want to use just the expiration dates that are displayed, or do you want to use all the expiration dates (even the hidden ones)?

 

[pao13]

Thank you for your answer again.
I have attached the file again and copied expiration dates (all of them) to column P above so that they are displayed. I want all the dates.

Let's explain what I'm trying to do.
I have some stock prices and I want to calculate the price of the options. To do that I need the time to expiration of each option in years. So in F33 what I want is (21/02/2014 - 02/01/2014)/365. For F33 the formula is correct. For F35 17/01/2014 is less than -let's say- 55 days from 21/02/2014. So the result in 35 needs to be (21/02/2014-17/01/2014). For F37 the result needs to be (21/02/2014-03/02/2014)/365. For F39 (21/03/2014-21/02/2014)/365.
I can do this manually but I would prefer to find a formula.

Thanks for any help!

 

[eibi]

Pao,

Does the attached do what you're asking?

 

[pao13]

Yes it does!

Thanks so much!