Tom’s Tutorials For Excel: Lookup Oldest and Newest Date

Tom’s Tutorials For Excel: Lookup Oldest and Newest Date

The picture shows one way of obtaining the oldest and newest dates in column B that correspond to the person’s name in column A that is in cell D2 as a lookup criteria. Because dates are numbers, you can use the MIN function for the oldest date, and the MAX function for the newest date.

The formula in cell E2 for the oldest date is =MIN(IF(A2:A26=D2,B2:B26)).
The formula in cell F2 for the newest date is =MAX(IF(A2:A26=D2,B2:B26)).

Array formulas are committed to a cell by pressing Ctrl+Shift+Enter, not just Enter.

Share Button
Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,
29 comments on “Tom’s Tutorials For Excel: Lookup Oldest and Newest Date
  1. sumit majumder says:

    I need a details in excel that if i put an unique code than it will retrieve last date of visit excluding the current date & if unique code row is blank than return 0

    A B
    Reg. No Date
    1000 17-Jan-18
    1000 17-Jun-18
    And if i put 1000 in A4 than result will come in C4 is 17-Jun-18

  2. sujit says:

    how to find second min date and max date up to n …. numbers date

  3. sujit says:

    Hi Tom

    i am asking about upper example of tome

    tome 1st oldest date and newest date find that is (feb17,2011 and july09,2011) similar what about tome 2nd oldest date and newest date and which formula use to fined 3rd, 4th…..n number of Oldest and newest date to find.

    • Tom Urtis says:

      You can use this formula:
      =LARGE(A1:A100,2) for the second largest (oldest)
      =LARGE(A1:A100,3) for the third largest (oldest)
      and so on.

      • Marshal says:

        Hi Tom, I have used this formula. But say for example the date range has repeating dates, e.g. 2 data fields have 2-Jun-23 as their dates. The formula does not distinguish between duplicate dates. Is there another formula which would factor in duplicate dates and accordingly give the oldest, 2nd oldest, 3rd oldest?

        • Tom Urtis says:

          I don’t understand. You mean you only want to rank duplicate dates as first oldest, second oldest, and so on? How would you know which duplicate date is younger or older than another duplicate date unless you don’t just mark the date but mark the date AND the time of each entry’s date. And what column are these dates in and what is the range of all the data? I suggest you ask this question on an Excel message board here, with these details where you can post screen shots. I contribute to that website but many other experts monitor it for free around the clock and can answer your question faster than I can, and they would definitely have a better way of seeing your workbook than I could. Its’ free and easy to become a member, and no spam.
          https://www.mrexcel.com/board/forums/excel-questions.10/

  4. Bryan says:

    Thank you! Just what I was looking for.

  5. Melissa says:

    Hello, could you tell me what the formula is to look for the minimum date in a range but that particular date has to be after a certain date?

  6. MMurphy says:

    Hello,

    What if I need to have the formula provide the value in D2(all unique values in column A) because there are too many to enter individually (as you have TOM listed in D2). I want the formula to provide the unique values from column A into my column D, rather than me manually inputting it. If that makes sense.

    • Tom Urtis says:

      Well, no, it does not exactly make sense because you wrote
      “I want the formula to provide the unique values from column A into my column D”
      when column D only shows in cell D2 what you want to find in column A.

      I do not know what you want, maybe a unique list of names from column A to be listed somewhere, but in any case, more detail from you can help me understand what you want to end up with.

  7. Pam Stazesky says:

    Hi Tom,
    How do you will down the column to get the oldest date for each person such as Bill, Bob, Mike, etc.? I know these names must be added to column D. I’m sure this simple, but I cannot figure it out. I just get my answer for my first line (Tom), but it puts Tom’s date for every single person.

    • Tom Urtis says:

      It could be done with a pivot table but to do it by a formula, actually two formulas, here are two pictures.

      In the top picture, to list the unique names of people in column D the array formula in cell G2 and copied down as needed is
      =INDEX($D$2:$D$18,MATCH(0,COUNTIF($G$1:G1,$D$2:$D$18),0))
      And for anyone wondering, yes I know, Advanced Filter can do this too, but I used an array formula in this case because I felt like it.

      In the bottom picture, to return each unique person’s oldest date, the array formula in cell H2 and copied down is
      =MIN(IF($D$2:$D$18=G2,$E$2:$E$18))
      To get the newest date instead of the oldest date, simply change the MIN to MAX.
      And for anyone wondering, yes I know about XLOOKUP, but I used an array formula in this case because I felt like it again.

      Recall that these are array formulas. You apply them with Ctrl+Shift+Enter, not just with Enter.

  8. Richard says:

    Hi Tom, great tip, thanks. Sorry for the late response to the thread (I’m new to the site). Is it possible amend the formula to pull the earliest date from a series of rows, if the data is continuous? Our system spits out a new row of data after any amendments to records, I’m often asked when the latest allowance first started. Example below:

    Ideally, I’d like to pull 01/06/2021 for row 1-3, and 01/01/2018 for row 4-5. Any help would be appreciated, even multiple column possibilities.

    Staff Member Start Date End Date Type Continous Date
    12345 01/12/2021 20/05/2022 Hardship
    12345 02/08/2021 30/11/2021 Hardship
    12345 01/06/2021 01/08/2021 Hardship
    12345 01/01/2019 30/04/2019 Hardship
    12345 01/01/2018 31/12/2018 Hardship

    • Tom Urtis says:

      I do not understand how you want the results to look. In my example I show a range that listed the criteria by count. You need to show what your expected result looks like and why it looks the way it looks.

  9. Richard says:

    Hi Tom, unfortunately I can’t add a picture with this on so it is hard to show. In the final column, row 2-4 would show 01/06/2021 for the start of the second continuous block, row 5-6 would show 01/01/2018

  10. Binsar says:

    Could you help me please, i want the result is the nearest older date or if there is the date there, then the result is that date. here is my data:

    A (Data)
    01/01/23
    05/01/23
    12/01/23
    31/01/23

    B (Input)
    11/01/23

    C (Formulated Result)
    05/01/23
    =IF(B1=A1:A4;A1:A4;MAX(IF($A$1:$A$4<B1;$A$1:$A$4)))

    It works for the date that are not in the data, but if i input 12/01/23, the result is still 05/01/23, and i want the result is 12/01/23. Please help. Thanks Tom!

    • Tom Urtis says:

      Your formula works for me.
      And when I enter 13/1/2023 in cell B1 the formula returns 12/01/23 as it should.

      Maybe you are not confirming the formula in cell C1 with Ctrl+Shift+Enter instead of just Enter

      or

      Maybe you are entering the 12/01/23 in maybe cell A5 which is outside of the range of cells in your formula.

      Whatever the case is, I cannot duplicate your problem. It works for me.

      • Binsar says:

        Dear Mr. Tom,

        Thanks for Answering!

        Yes it works if you input the date that doesn’t in the data (it will show the nearest oldest one). But i want the result if there is the date there (A1:A4), it will result the data instead.

        Here are the result i want:
        B1 (Input)
        11/01/23
        C1 (Formulated)
        05/01/23

        B1 (Input)
        12/01/23
        C1 (Formulated)
        12/01/23

        It seems the formula =IF(B1=A1:A4;A1:A4; doesnt work with MAX(IF($A$1:$A$4<B1;$A$1:$A$4))). Because if i only use formula =IF(B1=A1:A4;A1:A4;, it works for show the data, but if i add MAX(IF($A$1:$A$4<B1;$A$1:$A$4))) on the False Value on IF, it only reads the MAX(IF($A$1:$A$4<B1;$A$1:$A$4))).

        Looking forward for your help Mr. Tom, Tks.

        • Tom Urtis says:

          As I said, the formula and what you say you want all works for me.
          This makes no sense:
          “that doesn’t in the data”
          I say again which you have not confirmed, if you are applying the formula with Ctrl+Shift+Enter, NOT JUST with Enter.

  11. Cathy McQuitty says:

    Tom,
    No matter what I do, I am getting the oldest date of all dates in the range. No matter what my test data is. What am I doing wrong?

    UPDATE: Ha! I figured it out. I had hit enter, put my cursor back into the cell and hit Ctrl+Shift+Enter, but this did NOT work. What did work was to paste the formula (or type it, rather), and do not hit ENTER at all but use the Ctrl+Shift+Enter. The formula then displays like this in the cell {=MIN(IF(A$2:A$26=D2,B$2:B$26))}. Just thought I’d mention it in case anyone else has this trouble. 🙂

  12. Rhonda says:

    I need to look up dates in one column in one sheet and return oldest, next, next, next as applicable in another sheet based on a different column lookup value

    • Tom Urtis says:

      For a question like that, you should say…
      • what the sheet tab name is where the dates in one column are.
      • what the column is of the sheet tab name is where the dates in one column are.
      • what the starting row is in the column is of the sheet tab name is where the dates in one column are.
      • what the other sheet name is.
      • what the lookup column is of the other sheet tab name.
      • what the starting row is in the lookup column is of the other sheet tab name.
      • what exactly you mean by “next, next, next as applicable”. That can have a hundred different meanings. No one except you has seen your workbook.

  13. Nino says:

    hello,

    i have several policy numbers in one cell and different dates in another cell.
    i want to find out the okdest date.
    for example:
    Cell: A1 = 005/23 Cell: B1 = 01/09/2023
    Cell: A2 = 005/23 Cell: B2 = 05/12/2023
    Cell: A3 = 005/23 Cell: B3 = 23/11/2023

    • Tom Urtis says:

      =MIN(IF(A1:A3=”005/23″,B1:B3))
      If you are using Excel version 2016 then confirm the formula with Ctrl+Shift+Enter, NOT JUST WITH ENTER.
      Also note that if your formula returns a serial number such as 45114 then simply format that formula-containing cell as the custom date format you prefer which looks like DD/MM/YYYY.

Leave a Reply

Your email address will not be published. Required fields are marked *

*