Produce the most recent date

[Guest]

I have a stock sheet in which i keep track of the date that stock was
received, where the stock is stored and the total stock. I enter the date
that the stock was received and the date that it was entered into the storage
place. What i need is for the Total Stock sheet to automatically calculate
the most recent date the stock was received.

Basically i want to use a function to compare two dates and produce the most
recent one, but am not sure which function will do this as the one that i
know only do this for numbers in which the smallest or earliest figure is
obvious...

 

[T. Valko]

The MAX() function will return the most recent date:

A1 = 2/16/2007
A2 = 2/12/2007

=MAX(A1:A2)

Returns: 2/16/2007

If it returns something like 39129 just format as DATE.

Biff

 

[Guest]

This still produces the wrong date. For instance 23/1/07 is given instead of
16/2/07 because it is a "larger"

 

[David Biddulph]

In which case you haven't got dates in your spreadsheet, you've got text
strings. Change them to dates. One possibility is that you are using a
different date format from that which your Excel settings or your Windows
rehgional settings are expecting. Another possibility is that you've got
spare spaces or other non-printing characters in your text string. Another
possibility is that the cell was formatted as text before you put the data
in.

 

[Guest]

I am Australian and need the dates in the Australian format (dd,mm,yyyy).

Excel is set up to accept the Australian date format, and yes i do have the
format for the cells set to date.

Is there no way to produce the most recent date if it is not the US
format?????

 

[Pete_UK]

I use UK dates, and MAX works fine with them. Dates are only numbers
to Excel, so if your dates are proper dates then it should work ok. As
a check, select one of your date cells and format it as a number - if
it is a proper date then you should get something around 39000
showing. Format it back to date and try the same on the other date
cell.

Hope this helps.

Pete

 

[David Biddulph]

Excel will cope happily in Australian format (and that's the same as we have
here in England), so I guess that the problem is probably one of the things
I suggested earlier. For a date in cell A1 to sort, =ISNUMBER(A1) should be
TRUE and =ISTEXT(A1) should be FALSE. If the cells are text, check again
for spaces or other non-printing characters before or after the number.
To convert the text to dates, you could try Data/ Text to columns/
delimited/ out to last stage/ select Column data format: Date: DMY
Other options you could try are to put a zero in a spare cell, copy, and
Edit/ Paste special/ add (or a 1 and Edit/ Paste special/ multiply).