2nd largest date based on specific date

nbk95jg

New Member
Joined
May 5, 2011
Messages
21
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.:eek:

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. :oops:

Thanks for your help...
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Hey Marcelo, your formulas seem to work. For the NEXT DAY formula, you are limiting the rows to 1000, is there a way to make it work for anything in column B. Or do I just need to change it to B500000, and then keeping an eye out if I go over 500000?
 
Upvote 0
Do you really have 500K rows of data?

Try to adjust the ranges to the max possible number of rows (UNTESTED) and check if the performance is acceptable.

M.
 
Upvote 0
Woah!, Totally slows down my PC. Just sits there thinking... You have any other solutions?
 
Upvote 0
Is the expected value in the following record

11
11/05/14
987
Yes

<tbody>
</tbody>

correct?

Hi Aladin,
yes it is, Loan 987 was there the previous day (11/03), see row 7. There was no data for 11/04, so 11/03 becomes the next available PREVIOUS DAY.
Hope this helps.
 
Upvote 0
Woah!, Totally slows down my PC. Just sits there thinking... You have any other solutions?

Unfortunately, at this time I have no other good solution.
Maybe someone else can provide a macro or a better formula for column D (Next Day).

M.
 
Upvote 0
See if this formula is faster (my last shot)

D2 copied down
=COUNTIF(B3:INDEX($B$2:$B$500000,MATCH(INDEX(A2:$A$500000,MATCH(A2,A2:$A$500000)+1),$A$2:$A$500000)),B2)
 
Upvote 0
oops... formula correction

D2
=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)
 
Upvote 0

Forum statistics

Threads
1,213,549
Messages
6,114,264
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top