basically I have a column of name+date i.e. "John Smith 05/01/06". What
I need to do in a dummy column is put a formula that returns only the
date.

The problem I have is that some of the dates are in format 05/01/06 and
some are 05/01/2006 so the number of characters for the date is not
consistent (so i cannot just use the Right runction).

anyone know a way of doing this?

thanks


--
neowok
------------------------------------------------------------------------
neowok's Profile: http://www.excelforum.com/member.php...fo&userid=5940
View this thread: http://www.excelforum.com/showthread...hreadid=505307

=MID(A11,FIND("~",SUBSTITUTE(A11," ","~",LEN(A11)-LEN(SUBSTITUTE(A11,"
",""))))+1,99)

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"neowok" wrote in
message ...

basically I have a column of name+date i.e. "John Smith 05/01/06". What
I need to do in a dummy column is put a formula that returns only the
date.

The problem I have is that some of the dates are in format 05/01/06 and
some are 05/01/2006 so the number of characters for the date is not
consistent (so i cannot just use the Right runction).

anyone know a way of doing this?

thanks


--
neowok
------------------------------------------------------------------------
neowok's Profile:
http://www.excelforum.com/member.php...fo&userid=5940
View this thread: http://www.excelforum.com/showthread...hreadid=505307

=RIGHT(A1,LEN(A1)-FIND(" ",RIGHT(A1,LEN(A1)-FIND(" ",A1)))-FIND(" ",A1))
will give you the date as text

=DATEVALUE(RIGHT(A1,LEN(A1)-FIND(" ",RIGHT(A1,LEN(A1)-FIND(" ",A1)))-FIND("
",A1)))
will give you the date as an excel date (format as date to show a date)

You could also use the Data-Text to Columns function. Select delimited and
use a space as the delimiter.

"neowok" wrote:


basically I have a column of name+date i.e. "John Smith 05/01/06". What
I need to do in a dummy column is put a formula that returns only the
date.

The problem I have is that some of the dates are in format 05/01/06 and
some are 05/01/2006 so the number of characters for the date is not
consistent (so i cannot just use the Right runction).

anyone know a way of doing this?

thanks


--
neowok
------------------------------------------------------------------------
neowok's Profile: http://www.excelforum.com/member.php...fo&userid=5940
View this thread: http://www.excelforum.com/showthread...hreadid=505307

thanks thats worked nicely.


--
neowok
------------------------------------------------------------------------
neowok's Profile: http://www.excelforum.com/member.php...fo&userid=5940
View this thread: http://www.excelforum.com/showthread...hreadid=505307