Share via

Formula that returns the second to latest date

Holly Beazley 0 Reputation points
2026-03-12T16:25:21.1266667+00:00

Hi. I have a list of many dates and many names. I can get the latest date related to a name, but I need the second latest date.

Microsoft 365 and Office | Excel | For business | Windows
0 comments
Answer accepted by question author
  1. Hendrix-C 12,120 Reputation points Microsoft External Staff Moderator
    2026-03-12T16:58:48.7333333+00:00

    Hi @Holly Beazley,

    Thank you for posting your question in the Microsoft Q&A forum.

    According to your concern, the formula provided by Q&A Assist does have the correct formula of using LARGE+FILTER to returns the second latest date for a given name. However, that formula does not exclude duplicates dates, so if the data has two same latest dates for a given name, it still returns the latest one.

    In case you want to return the second latest distinct date (ignore duplicates), you can try this formula:

    =LARGE(UNIQUE(FILTER($B$2:$B$10,$A$2:$A$10=$D$2)),2)

    User's image

    Hope this information will help. Please follow these steps and let me know if it works for you. If you have any questions or need further assistance, please feel free to share them in the comments so I can continue to support you.

    Thank you for your patience and understanding. I'm looking forward to your reply.

    If the answer is helpful, please click "Accept Answer" and kindly upvote it.

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    1 person found this answer helpful.

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Q&A Assist
    2026-03-12T16:25:31.7833333+00:00
    AI generated content. This question contains an answer created with AI from Q&A Assist. Learn more

    Use a combination of LARGE (or SORT) with FILTER to get the second latest date for a given name.

    Assume:

    • Names are in A2:A100
    • Dates are in B2:B100
    • The name to search is in D1
    1. Second latest date only:
    =LARGE(FILTER($B$2:$B$100,$A$2:$A$100=$D$1),2)
    • FILTER keeps only dates where the name matches D1.
    • LARGE(...,2) returns the 2nd largest (second latest) date.
    1. If dynamic arrays are not available, use AGGREGATE:
    =AGGREGATE(14,6,$B$2:$B$100/($A$2:$A$100=$D$1),2)
    • Function 14 = LARGE
    • Option 6 ignores errors
    • The division creates an array of dates only for matching names; others become errors and are ignored.

    Format the result cell as Date if it shows as a number.

    References:

    0 comments

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.