Solved

# Excel Formula - Get max date from range

Posted on 2012-09-12
881 Views
Hi,

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

0
Question by:victoriaharry

LVL 50

Accepted Solution

Hello,

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

=IF(MAX(B4:L4)<TODAY(),MAX(B4:L4),MIN(B4:L4))

Copy down.

cheers, teylyn
0

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
0

LVL 17

Assisted Solution

Hey, teylyn was close....it should be put in an array to exclude the zeros, like this:
{=IF(MAX(G10:M10)<TODAY();MAX(G10:M10);MIN(IF(G10:M10>0;G10:M10)))}

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

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

LVL 50

Expert Comment

Hello,

>> 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
27863146.xlsx
0

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
0

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.
0

## Featured Post

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.