Thanks:  0
Likes:  0

# Thread: 2nd largest date based on specific date

1. ## 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:
 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

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.

2. ## Re: 2nd largest date based on specific date

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"))`

3. ## Re: 2nd largest date based on specific date

C2, just enter and copy down:
Code:
```=IF(ISNUMBER(MATCH(\$B2,\$B\$1:B1,0)),"Yes","No")
```
D2, just enter and copy down:
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"))
```

4. ## Re: 2nd largest date based on specific date

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.

5. ## Re: 2nd largest date based on specific date

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.

6. ## Re: 2nd largest date based on specific date

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

7. ## Re: 2nd largest date based on specific date

Originally Posted by nbk95jg

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.

8. ## Re: 2nd largest date based on specific date

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.

9. ## Re: 2nd largest date based on specific date

 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 11 11/05/14 987 Yes 12 11/05/14 258 No 13 11/05/14 852 No 14 11/05/14 951 No 15 ETC ETC ETC ETC

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.

10. ## Re: 2nd largest date based on specific date

Have you tried the formulas i've posted in #8?

M.

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•