Thanks Thanks:  0
Likes Likes:  0
Page 1 of 3 123 LastLast
Results 1 to 10 of 25

Thread: 2nd largest date based on specific date

  1. #1
    New Member nbk95jg's Avatar
    Join Date
    May 2011
    Posts
    21
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Thanks for your help...

  2. #2
    Board Regular
    Join Date
    Nov 2011
    Location
    San Jose, CA
    Posts
    2,277
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #3
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,915
    Post Thanks / Like
    Mentioned
    43 Post(s)
    Tagged
    8 Thread(s)

    Default 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"))
    
    Assuming too much and qualifying too much are two faces of the same problem.

  4. #4
    New Member nbk95jg's Avatar
    Join Date
    May 2011
    Posts
    21
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #5
    New Member nbk95jg's Avatar
    Join Date
    May 2011
    Posts
    21
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: 2nd largest date based on specific date

    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.

  6. #6
    New Member nbk95jg's Avatar
    Join Date
    May 2011
    Posts
    21
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #7
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,915
    Post Thanks / Like
    Mentioned
    43 Post(s)
    Tagged
    8 Thread(s)

    Default Re: 2nd largest date based on specific date

    Quote Originally Posted by nbk95jg View Post
    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.
    Assuming too much and qualifying too much are two faces of the same problem.

  8. #8
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    15,308
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    6 Thread(s)

    Default 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. #9
    New Member nbk95jg's Avatar
    Join Date
    May 2011
    Posts
    21
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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

    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.

  10. #10
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    15,308
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    6 Thread(s)

    Default Re: 2nd largest date based on specific date

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

    M.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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