Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Select Highest Date

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Posts
    62
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I have 3 dates in 3 cells. Ie. 06/12/2001 cell H3, 24/11/01 in cell I3 and 06/01/01 in J3.
    Is it possible to select the latest date of the 3 - I wish to show this in G3. Any ideas?
    Many thanks

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Denver, CO
    Posts
    1,743
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-18 08:24, MattB wrote:
    I have 3 dates in 3 cells. Ie. 06/12/2001 cell H3, 24/11/01 in cell I3 and 06/01/01 in J3.
    Is it possible to select the latest date of the 3 - I wish to show this in G3. Any ideas?
    Many thanks
    you can use
    =MAX(H3:J3)

  3. #3
    Board Regular
    Join Date
    Mar 2002
    Posts
    62
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Many thanks - easy when you know how!
    One more thing - is it possible to show which cell gives the return. If for instance I3 was the latest date - could the return in G3 also show this?

  4. #4
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hard to do that without creating a circular reference. Easy to do in VBA. Try the following code to get the cell address:

    =ADDRESS(3,MATCH(G3,H3:J3,0)+7,4)

    The code assumes that the lookup range is H3:J3 and that the max is in cell G3.

    Best regards,
    Allan Chara
    http://www.mrspreadsheets.com

  5. #5
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sunny, spring-like Hull
    Posts
    3,339
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Building on Al's formula a bit, the following will show the latest date and the cell that it's in:-

    =TEXT(MAX(H3:J3), "dd/mm/yyyy") &" "&ADDRESS(3,MATCH(MAX(H3:J3),H3:J3,0)+7,4)

  6. #6
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Good thought Mudface, I didn't even think of that.

  7. #7
    Board Regular
    Join Date
    Mar 2002
    Posts
    62
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Sorry Al, what do the 3, 7 & 4 denote?

  8. #8
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sunny, spring-like Hull
    Posts
    3,339
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Replying on Al's behalf: -

    3 = Row number
    7 = offset of the column the formula is in from A (eg if your formula was in column B, this would equal 1).
    4 = return relative cell reference.

    Check out the help on the Address function for more info.

  9. #9
    Board Regular
    Join Date
    Mar 2002
    Posts
    62
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Al / Mudface

    Lastly!, I need to copy this thro' approx. 2000 rows, excuse my ignorance again but the ADDRESS (3 - obviously needs to match the row number, when i copy & paste this stays as 3 - how can I change this?

    TEXT(MAX(H3:J3), "dd/mm/yyyy") &" "&ADDRESS(3,MATCH(MAX(H3:J3),H3:J3,0)+7,4)


  10. #10
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sunny, spring-like Hull
    Posts
    3,339
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Ah. Do you have 3 different dates in each row or do you just want a reference returned to your dates in H3:K3?

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
  •