2nd largest date based on specific date

[nbk95jg]

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

	A	B	C	D
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

<tbody>
</tbody>

Previous Day formula needs to check if the loan appeared on the day prior;
Example:
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.

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;
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.

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.

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.

Thanks for your help...

 

[Marcelo Branco]

Sorry, forgot to put the If
=IF(COUNTIF(...),"Yes","No")

Worked for me

	A​	B​	C​	D​
1​	Date​	Loan​	Previous Day​	Next Day​
2​	30/10/2014​	123​	No​	No​
3​	02/11/2014​	321​	No​	Yes​
4​	02/11/2014​	456​	No​	No​
5​	02/11/2014​	987​	No​	Yes​
6​	03/11/2014​	123​	No​	No​
7​	03/11/2014​	987​	Yes​	Yes​
8​	03/11/2014​	357​	No​	No​
9​	03/11/2014​	321​	Yes​	No​
10​	05/11/2014​	789​	No​
11​	05/11/2014​	987​	Yes​
12​	05/11/2014​	258​	No​
13​	05/11/2014​	852​	No​
14​	05/11/2014​	124​	No​

D2
=IF(COUNTIF(B3:INDEX($B$2:$B$500000,MATCH(INDEX($A$2:$A$500000,MATCH(A2,$A$2:$A$500000)+1),$A$2:$A$500000)),B2),"Yes","No")

M.

 

[nbk95jg]

Not sure what I am doing wrong then, but if I put the formula =IF(COUNTIF(B3:INDEX($B$2:$B$500000,MATCH(INDEX($A$2:$A$500000,MATCH(A2,$A$2:$A$500000)+1),$A$2:$A$500000)),B2),"Yes","No") in D2, I still get all NO's

 

[Marcelo Branco]

hmmm... i dont know why is not working for you

Well, the dates in my data sample are formatted as dd/mm/yyyy because this is my regional settings for dates. ( i dont think it should be an issue since the dates in your data are real dates)

I'm assuming the dates in ascending order

M.

 

[nbk95jg]

hmmm... i dont know why is not working for you

Well, the dates in my data sample are formatted as dd/mm/yyyy because this is my regional settings for dates. ( i dont think it should be an issue since the dates in your data are real dates)

I'm assuming the dates in ascending order

M.

Dates are in ascending order. Not sure why either, I have Excel 2007. I tried it several times. Not sure if a '$' is missing or too much somewhere. But if it were, then the formula wouldn't work for you. One for the mystery books maybe?
I will use your previous formula and see if something can be done with the amount of data given to me.

 

[Marcelo Branco]

You can download my file with the new formula from

https://app.box.com/s/dwu7n9nxfxw9lwlmcgae

M,