?
Solved

Excel Formula - Get max date from range

Posted on 2012-09-12
6
Medium Priority
?
1,243 Views
Last Modified: 2012-09-13
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

screenshot
0
Comment
Question by:victoriaharry
  • 3
  • 2
6 Comments
 
LVL 50

Accepted Solution

by:
Ingeborg Hawighorst (Microsoft MVP / EE MVE) earned 1000 total points
ID: 38393590
Hello,

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

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

Copy down.

cheers, teylyn
0
 

Author Comment

by:victoriaharry
ID: 38393631
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

by:ExcelGuide
ExcelGuide earned 1000 total points
ID: 38393941
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 50
ID: 38394037
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

by:victoriaharry
ID: 38394222
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
ID: 38394271
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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

864 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