find latest date in row, when max function doesn't work

[brakbek]

hello everybody,

I have the following problem, i Need to find te latest date in a row.
the format of the date is like 10-06 and for example 11-'06.
Because of the " ' " in the last date, the max function returns 0 as
value.

Does anybody know how to solve this Problem?

 

[brakbek]

Jon von der Heyden schreef:

I suspect in this instance a find and replace on the " ' " will work.
Ctrl+H | Replace: " ' " with "" (i.e. without the inverted comma's)!

HTH

Jon


--
Jon von der Heyden
------------------------------------------------------------------------
Jon von der Heyden's Profile: http://www.officehelp.in/member.php?userid=4852
View this thread: http://www.officehelp.in/showthread.php?t=1265157

Posted from - http://www.officehelp.in

that is completely correct Jon,
i replaced the inverted comma's with spaces
only problem remains that the beginning of the cell also has
an inverted comma, like '10-'06. Excell replaces the inverted comma
before '06
but in won't delete the one which stands in the beginning of the cell
i'am thinking of a macro which does this.
But I can't get it to work properly.

 

[Pete_UK]

Assuming your dates are in A1 to D1, try this array* formula:

=MAX(DATE("20"&RIGHT(A11,2),LEFT(A11,2),"01"))

Adjust A11 to suit your data.

* As this is an array formula, then once you have typed it in (or
subsequently edit it) you must use CTRL-SHIFT-ENTER (CSE) rather than
just ENTER to commit it. If you do this correctly then Excel will add
curly braces { } around the formula when viewed in the formula bar -
you must not type these yourself.

Hope this helps.

Pete