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

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
These formulas require the date column to be in ascending order:

Enter in C2 and copy down:
Code:
=IF(COUNTIF(B$2:B2,B2)=1,"No","Yes")

Enter in D2 and copy down:
Code:
=IF(A2=TODAY(),"",IF(COUNTIF(B2:B$8,B2)=1,"No","Yes"))
 
Upvote 0
C2, just enter and copy down:
Rich (BB code):
=IF(ISNUMBER(MATCH($B2,$B$1:B1,0)),"Yes","No")
D2, just enter and copy down:
Rich (BB code):
=IF(ROW()-ROW($B$2)+1=ROWS($B$2:$B$8),"No",
  IF(ISNUMBER(MATCH($B2,$B3:INDEX($B$2:$B$8,ROWS($B$2:$B$8)),0)),
  "Yes","No"))
 
Upvote 0
Hey Ron, The problem with your formula is that is doesn't limit itself to only the previous day, or next day. A loan entered on 11/05 and on 11/03 will come back with YES, even though there is 11/04 data. I need to formula to stop at the previous day, not look at all previous days. Same for Next Day, it looks it any day passed the date entered, instead of only the next day itself.
 
Upvote 0
Hey Aladin, your formula seems to do the same as Ron's..

The problem with your formula is that is doesn't limit itself to only the previous day, or next day. A loan entered on 11/05 and on 11/03 will come back with YES, even though there is 11/04 data. I need to formula to stop at the previous day, not look at all previous days. Same for Next Day, it looks it any day passed the date entered, instead of only the next day itself.
 
Upvote 0
Also, I am not limited to B8, I have an undisclosed number of rows. So I need to check the entire column B (and A).
 
Upvote 0
Hey Aladin, your formula seems to do the same as Ron's..

The problem with your formula is that is doesn't limit itself to only the previous day, or next day. A loan entered on 11/05 and on 11/03 will come back with YES, even though there is 11/04 data. I need to formula to stop at the previous day, not look at all previous days. Same for Next Day, it looks it any day passed the date entered, instead of only the next day itself.

Did you try them at all? They yield exactly the same results that you posted. If still incorrect, try to create a sample that reflects your demands.
 
Upvote 0
Not sure if i understand correctly what you need.

See if these work

C2 copied down
=IF(COUNTIFS(A1:A$2,IFERROR(INDEX(A1:A$2,MATCH(A2,A1:A$2,0)-1),A1),B1:B$2,B2),"Yes","No")

D2 copied down
=IF(COUNTIFS(A3:$A$1000,IFERROR(INDEX(A3:$A$1000,MATCH(A2,A3:$A$1000)+1),A3),B3:$B$1000,B2),"Yes","No")

Hope this helps

M.
 
Upvote 0
A
B
C
D
1
Date
Loan
Previous Day
Next Day
2
10/30/14
123
No
No
3
11/02/14
321
No
Yes
4
11/02/14
456
No
No
5
11/02/14
987
No
Yes
6
11/03/14
123
No
No
7
11/03/14
987
Yes
Yes
8
11/03/14
357
No
No
9
11/03/14
321
Yes
No
10
11/05/14
789
No
<blank></blank>
11
11/05/14
987
Yes
<blank></blank>
12
11/05/14
258
No
<blank></blank>
13
11/05/14
852
No
<blank></blank>
14
11/05/14
951
No
<blank></blank>
15
ETC
ETC
ETC
ETC

<tbody>
</tbody>

Aladin,
Your PREVIOUS DAY formula returns a YES in C6. The loan in row 6 has a date of 11/03, the previous day in this report is 11/02, so C6 should be a NO. I believe your formula reads that the loan is there on 10/30, so it puts a YES. But 10/30 is not a previous day in this instance. Only one (1) day prior to the date in A6 is considered previous day.

Your NEXT DAY formula returns a YES in D2, but it should be a NO. the Loan in B2 (from 10/30) does not appear on the next day (11/02), it does appear on 11/03, but that is no longer considered next day.

The formula for todays loans (11/05) in NEXT DAY should remain blank. Your formula returns a NO.

I am not limited to 14 rows, your formula reads $B$2:$B$8, which I did change to B:B.

Tom, your formulas do the same thins as Aladin's do. And your NEXT DAY formula limits the amount of rows (B2:B$8,B2), I need the formula to work for an undisclosed/unlimited amount of rows.

Hope this example helps.
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,317
Members
448,564
Latest member
ED38

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