Excel: 2nd largest date based on specific date

I have 2 data columns (Date and Loan) and 2 columns (Previous Day and Next Day) needing formulas:

1 Date Loan Previous Day Next Day
2 10/30/14 123 No Yes
3 11/03/14 321 No No
4 11/03/14 456 No Yes
5 11/03/14 123 Yes No
6 11/05/14 654 No
7 11/05/14 789 No
8 11/05/14 456 Yes
Previous Day formula needs to check if the loan appeared on the day prior; [I]Example:[/I] [I]So B5 entered on 11/03 appeared also on 10/31, so it should get a YES in C5. etc. Same for B8, it was entered on 11/05 and appeared on the previous day, even though it was not on 11/04, the previous day here was 11/03. [/I] Next Day formula needs to do the same, except for the day after. Of course 11/05 will be empty, since there is no next day yet; [I]Example: Just like B8 and B4. B4 was entered on 11/03. Since there is no 11/04, the next day is 11/05. So B4 will get a YES in D4. [/I] Problem is that the date column can skip days. As seen by going from 11/03 to 11/05. Therefore I cannot use the WORKDAY -1 formula.:eeek: Previous Day column example: in C3 I need a formula that can calculate the next lowest date based on the date in A3. Next Day column example: in D4 I need a formula that can calculate the next highest date based on the date in A4. I hope to stay away from array formulas since they seem to slow down my excel book. :banghead: Thanks for your help...

This question generated 11 answers. To proceed to the answers, click here.

This thread is current as of November 05, 2014.

For more resources for Microsoft Excel