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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

brakbek - 19 Nov 2006 09:49 GMT

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?

Reply to this Message

Jon von der Heyden - 19 Nov 2006 10:59 GMT

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 Heyde

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

Reply to this Message

brakbek - 19 Nov 2006 14:41 GMT

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)!
[quoted text clipped - 10 lines]
>
> 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.

Reply to this Message

Pete_UK - 20 Nov 2006 09:55 GMT

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

=MAX(DATE("20"&RIGHT(A1:D1,2),LEFT(A1:D1,2),"01"))

Adjust A1:D1 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

> Jon von der Heyden schreef:
>
[quoted text clipped - 21 lines]
> i'am thinking of a macro which does this.
> But I can't get it to work properly.

Reply to this Message