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

by Tom Urtis 12 Comments ↓

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.

‹ Tom’s Tutorials For Excel: Parse a Cell’s Contents

Tom’s Tutorials For Excel: COUNTIF and SUMIF Anything or Nothing ›

Posted in Tom's Tutorials for Excel
Tags: Analytics, Big Data, Business Intelligence, Cells Ranges Lists, Data Science, Date Time, Excel Expert, Excel Guru, Formulas Functions, Microsoft, Microsoft Excel, Microsoft MVP, Microsoft Office, Tom Urtis

12 comments on “Tom’s Tutorials For Excel: Lookup Oldest and Newest Date”

1. sumit majumder says:

   June 17, 2018 at 11:41 am

   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

   Reply
   • Tom Urtis says:

     June 17, 2018 at 11:59 am

     =IF(LEN(A4)=0,"",MAX(0,MAX(IF(A2:A3=A4,B2:B3))))

     This is an array formula, applied to a cell with Ctrl+Shift+Enter, NOT just with Enter.

     Reply
2. sujit says:

   January 15, 2020 at 11:05 pm

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

   Reply
   • Tom Urtis says:

     January 16, 2020 at 8:40 am

     Maybe this is what you are asking about.

     

     Reply
3. sujit says:

   January 16, 2020 at 8:37 pm

   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.

   Reply
   • Tom Urtis says:

     January 19, 2020 at 9:53 am

     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.

     Reply
4. Bryan says:

   October 21, 2020 at 9:01 am

   Thank you! Just what I was looking for.

   Reply
   • Tom Urtis says:

     October 21, 2020 at 10:02 am

     Thanks, Bryan!

     Reply
5. Melissa says:

   February 17, 2021 at 7:37 pm

   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?

   Reply
   • Tom Urtis says:

     February 18, 2021 at 10:02 am

     With dates in range A2:A14 and an “after date” in cell C1, this array formula (Ctrl+Shift+Enter) should do what you want:
     =MIN(IF(A2:A14>C1,A2:A14))

     Reply
6. MMurphy says:

   April 28, 2021 at 7:07 am

   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.

   Reply
   • Tom Urtis says:

     April 28, 2021 at 8:21 am

     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.

     Reply