fbpx
Search
Close this search box.

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

Share

Facebook
Twitter
LinkedIn

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

Facebook
Twitter
LinkedIn

Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Overall I learned a lot and I thought you did a great job of explaining how to do things. This will definitely elevate my reporting in the future.
Rebekah S
Reporting Analyst
Excel formula list - 100+ examples and howto guide for you

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Advanced Pivot Table tricks

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

44 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) ..."

        • KatatoniaUK says:

          Hi Adam,

          I am trying to use this but not having success. Do you *have* to have your data in an Excel table? Or can you just have it in the spreadsheet like normal?
          Using your example above, I am trying to have the raw data on one sheet (so your table above on one sheet) and then on a second sheet have a table where each fruit appears only once and the formula returns the last date that the price was changed for each fruit. So I would want the formula to reference the fruit name and then look for it on the other sheet and return the last date in the cell. Like a LOOKUP function... Does that make sense?

          Thanks for your help.

    • stumped808 says:

      stumped, do you think you can assist with the price formula?

  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?

  23. Ravi Bugadikatti says:

    How do we get the highest value enter in one cell (not in a column)?
    For e.g. particular cell say A1 captures the date. First time it is updated it would have values of 9/8/15. If again next it gets updated it will now have values of 9/8/15, 10/8/15 so on...
    How can be the highest date captured?

  24. Roger says:

    Here's the format on Sheet1 of Excel

    ? suppose Columns are in A, B, C, D respectively.

    S. No. Box Name Arrival Date No. of Pakgs

    1 Box 1 19-Oct-15 4
    2 Box 2 19-Oct-15 4
    3 Box 3 20-Oct-15 5
    4 Box 4 07-Nov-15 5
    5 Box 5 08-Nov-15 4

    I want to every package delivery date in below format, Let suppose in column F, G, H and I respectively.

    Last Delivery Date 3rd Delivery 2nd Delivery 1st Delivery

    The Master data is in Sheet2, having column box name, arrival date, delivery date, goods type etc

    Please nelp me, how to get desire result.

  25. ramya says:

    HI,

    I would like t find out nearest date from today(before and after today)

    regards,
    Ramya.

Leave a Reply