Use MAX to find latest date in a list [Quick tip]

Posted on June 14th, 2012 in Excel Howtos - 37 comments

Here is a quick tip that I learned while conducting training classes in Australia.

If you have several dates in a range and you want to find out what the latest date is, just use MAX, like:

=MAX(A1:A10) would give you the latest date.

A Question…,

Assuming you have some dates (not necessarily sorted) in column A, which formula finds the last date (not latest)?
Bonus question: What if there are some gaps (cells with no value)? How would you find the last date?

Go ahead and post your answers in comments. Or share your favorite formula to find latest date in a range.

PS: My Australian trip is over now. On a train from Melbourne to Sydney now and will be leaving to Vizag via Bangkok (and Hyderabad) early tomorrow morning. I am very happy how the whole thing went. More on this later next week.

PPS: More tips on dealing with date & time values in Excel

Your email address is safe with us. Our policies

Written by Chandoo
Tags: , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

37 Responses to “Use MAX to find latest date in a list [Quick tip]”

  1. Abhishek says:

    =INDEX(A:A,IF(ISERROR(MATCH(9.999999E+306,A:A)),MATCH(“*”,A:A,-1),IF(ISERROR(MATCH(“*”,A:A,-1)),MATCH(9.999999E+306,A:A),MAX(MATCH(9.999999E+306,A:A),MATCH(“*”,A:A,-1)))))

    This seems to do it. You can use TEXT() function to format the date to your liking. 

  2. Raj says:

    I just checked a simple small or min works – even with the gaps in between but ofcourse it has to be in excel date format.

    Regards,
    Rajib

    • suresh says:

      Hi Raj

      ur right just max or min should work, it need not be in date format..reason is date is ultimately number and min and max should give results

  3. Luke M says:

    Last date with or without gaps:
    =INDEX(A:A,MATCH(9E99,A:A)) 

  4. Gregor Erbach says:

    A date in Excel is just the number of days since 1 Jaunary 1900. If you can use an Excel function on a number, you can also use it on a date.
    To see what is going on, just change the cell format from “date” to “number”. Hours, minutes and seconds are just decimal fractions of days.
    Excel does not display dates before 1/1/1900 (I guess this is in order to avoid any complications with the various calendar reforms that have occurred throughout history).
     

  5. Eric says:

    {=MAX((MyRange<(TODAY()+1))*MyRange)}

    Where MyRange is the the data.  The curly braces indicate that you’ve CTRL-ALT-ENTERed the formula.  It looks to me that you’ve got to have a contiguous range for it to work, but it handles spaces.  If you want the last day before today, delete the “+1″.

  6. Ben Niebuhr says:

    I assumed that you were asking for the last date listed, with no regard to if is is the most or least current date. So this is really just a way of finding the last populated cell in a column.

    {=MAX(IF(NOT(ISBLANK(A:A)),ROW(A:A),””))}

    From here you could bolt on an =Address() and a =indirect() if you waned to get the location or value.

    {=INDIRECT(ADDRESS(MAX(IF(NOT(ISBLANK(A:A)),ROW(A:A),””)),1))}

    Thanks for these challenges, Chandoo.
    Ben

  7. Janice Hoffman says:

    Select the range including empty cells and do a conditional formatting using top 10 and choose 1 in the dialog box making it only the highest number (latest date). It will highlight the latest date in the selected range.

  8. John san jose ca says:

    THANK YOU!!!

  9. Behold, a less than elegant solution:

    {=INDIRECT(“A” & MAX((A:A>1)*ROW(A:A)))}

    Alternatively, you can use “Index(a:a…” but I wanted to spice things up a bit 

  10. Adam says:

    On the topic of Max dates, I use this particular array function quite frequently. It finds the latest date for a particular item. Almost like a ‘MaxIf’

    Suppose you have a table of prices for 3 fruit which also contains historical prices:

    E.g.

    Date            Fruit               Price
    01/01/12   Apple           $1.00
    01/01/12   Banana         $3.00 
    01/01/12   Cantaloupe   $4.00
    03/10/12   Apple           $1.35
    04/11/12   Banana         $2.80 
    06/12/12   Cantaloupe   $4.05 

    The following formula will return the latest date for which the apple price was entered (04/11/12)
    {MAX(([@[Fruit]]=”Apple”)*([Date]))}
    As it’s an array formula, Ctl+Shift+Enter is required to get the curly braces.
    Formula Names assume the data is formatted in a Excel 2010 table. It works fine with standard references too. 

    Further witchcraft can get the formula to return the latest price if required.
     

    • Godsbod says:

      I think you are mixing your apples and bananas with your description of the last date for apples, bit of a fruit salad kicking in?

      • Adam says:

        Right you are!

        Although the health benefits of fruit salad are undisputed, the above should have read “The following formula will return the latest date for which the apple price was entered (03/10/12) …”

  11. William Jones says:

    Use the function LASTROW() from Morefunc.  It displays the last value in a column.

  12. ahmed says:

    Hi

    For me the formula =INDEX(A:A,MATCH(9E99,A:A))  gave me the last day in a series not the latest date in calendar 

    • Kyle McGhee says:

      That is what Chandoo asked in the topic, to find the last day entered in the series, not the latest and that formula will provide the required solution.
       
      Kyle

  13. suresh says:

    We can also use =LOOKUP(1000000000,B:B) to get the last  value in entered in column B

    • Luke M says:

      Nice! I see now that we could use
      =LOOKUP(9E99,B:B)
      for latest number, and
      =LOOKUP(“zzz”,B:B)
      for latest text.
      Cool tip! 

  14. DJ says:

    Hi Jordan Goldmeier,

    Thanks for the formula but when I tried it was giving an #Name error. However, when I tried {=INDIRECT(ADDRESS(MAX((A2:A22>1)*ROW(A2:A22)),1))} it worked.

    Thanks

    • Remember that when you copy and paste from WordPress, the quotation marks come in as fancy, slanted-quotes, which Excel treats as characters and not quotes. If you replace the quotes in the Excel formula bar, then press CTRL+SHIFT+ENTER, it should work. 

  15. Matt says:

    =OFFSET(“first date in colum”,SUM(COUNTA(“date range”),COUNTBLANK(“date range”),0))

    • Matt says:

      ***EDIT
      Got a little too hasty typing my response.

      If you know the range (A2:A100) this will give you the last entry. If you don’t know the last cell in the range…well you will need a more clever solution. 

      =OFFSET(A2, SUM(COUNTA(A2:A100), COUNTBLANK(A2:A100))-1,0)
         

  16. sam says:

    Array enter
    =MAX(MATCH({“?”,9.9E+307},A:A))

    Will handle Mixed data types ( Text/Numbers/both )

  17. Godsbod says:

    I looked at all this 9E99 stuff and could not get it to work, but knew what it was I really wanted to do.

    I needed to find the row that had the last actual item in it from the column in question.

    Then I had to return the data that was in that particular row.

    If I used a helper column (b) on the first row, this would have contained the formula: =(A1<>””) giving me a column that was filled with true or false statements.

    As these are numerical, I multiplied them in another column, by the row number that I was on, ie first row formula: =ROW(A1)*(A1<>””)

    I now have a column of numbers that are either ’0′ or the ‘row number’.

    I can use the ‘MAX’ command to extract the row with the last data.

    Taking all this into an array formula gives my solution to the problem and actually dispenses with the extra columns of data by virtualising them within the array formula.

    Hence: {=INDEX(A:A,MAX(ROW(A:A)*(A:A<>””)))}

  18. Kdu Bonalume says:

    Hi guys.

    What if I say that I have a range o dates and I wanna know the OLDEST date by a given month?

    I couldn’t answer it. Just could find the NEWEST with this formula:
    {=MAX(–(MONTH(SHEET1!$D:$D)=5)*( SHEET1!$D:$D ))} 
    The MAX formula will answer it correctly because the Zeros given to the non-match cases won’t interfere, but in the MIN formula, they will, giving “01/00/00″ as response.

    Can anybody give me a hand on it? I guess it’s a very nice challenge.
     

  19. Roger L Moreno says:

    Assuming:
    1)my dates are in range A1:A100
    2)no blank lines in between
    3) dates not sorted
    I use the following formula:
    =INDEX(A1:A100,COUNTA(A1:A100))
    And it gives me la last date in the range as recuested by Chandoo

  20. M Srinivas says:

    how to get the latest and oldest date if I have date format like below:
    2013-10-21 00:00:00
    2013-11-27 00:00:00
    2014-02-01 00:00:00
    2014-02-21 00:00:00
    2014-03-21 00:00:00
    2014-06-02 00:00:00

  21. Badwolf says:

    I have a similar question to Adam’s above, but I’m not able to get the same results. Let’s say my daughter wears a different shirt everyday and I want to know the last date she wore her green shirt:

    Date Shirt
    8/21/14 Green shirt
    8/22/14 Blue shirt
    8/23/14 Pink shirt
    8/24/14 Red shirt
    8/25/14 Green shirt

    I’m trying this formula, but I’m getting the #REF! error:

    =INDEX(MAX(A:A),MATCH(“Green shirt”,B:B,0))

  22. Brett Ellingson says:

    I’m not at my computer to test but did you make it an array formula by hitting ctrl+shift+enter?

Leave a Reply