Excel Formula - Get max date from range

Posted on 2012-09-12
Last Modified: 2012-09-13

I have a spreadsheet similar to the attached sample. I need a formula ro return the latest date from a range of cells which is less than the current date. If all dates in the range are later than the current date then it should return the minimum date. This is hard to explain so if todays date is 13/9/2012 and a dates in the cells are 12.1.2012 & 14/8/2012 then the formula should return 14/8/2012. If the dates in the cells are 12/11/2012 and 14/12/2012 then the formula should return 12/11/2012.
Hope this makes sense

Question by:victoriaharry
    LVL 50

    Accepted Solution


    could you post the spreadsheet instead of a screenshot?

    Are we looking at the whole range? Or row by row? Or column by column? Where would you want to see the result?

    In case we are looking at the data row by row, then maybe


    Copy down.

    cheers, teylyn

    Author Comment

    Thanks, This looks pretty close

    What I'm finding is if the Min condition kicks in then the date returned is 00/01/1900. I think this is because it is taking into account the blank cells in the range. Is there anyway of skipping over these so it actually returns the min date
    LVL 17

    Assisted Solution

    Hey, teylyn was should be put in an array to exclude the zeros, like this:

    click on the formula and press ctrl+shift+enter to make an array

    I've tested this and works like a charm :-)
    LVL 50

    Expert Comment


    >> I think this is because it is taking into account the blank cells in the range.

    if the "blank cells" are really blank, then the MIN() function will return the smallest REAL date. See attached file. This works in Excel 2003, 2007 and 2010.

    That's why I asked for a spreadsheet instead of a screenshot. From a screenshot it's impossible to tell what a cell actually contains.

    If, as it seems, the cells are not blank, but contain a zero, then, of course Psychotec's tweak is the ticket.

    cheers, teylyn

    Author Comment

    Thanks Guys,
    I will test this out at work tomorrow. FYI, all the cells in the range are formatted as custom (dd/mm/yyyy) if this makes a difference
    LVL 50

    Expert Comment

    Happy to hear how you got on. For what it's worth, the format of the cell does not matter. It's the value that counts. Format is just window-dressing.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
    Outlook Free & Paid Tools
    This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
    This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now