software

basically I have a column of name date i.e. quot;John Smith 05/01/06quot;. 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: www.excelforum.com/member.php...foamp;userid=5940
View this thread: www.excelforum.com/showthread...hreadid=505307=MID(A11,FIND(quot;~quot;,SUBSTITUTE(A11,quot; quot;,quot;~quot;,LEN(A11)-LEN(SUBSTITUTE(A11,quot;
quot;,quot;quot;)))) 1,99)

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

quot;neowokquot; gt; wrote in
message ...
gt;
gt; basically I have a column of name date i.e. quot;John Smith 05/01/06quot;. What
gt; I need to do in a dummy column is put a formula that returns only the
gt; date.
gt;
gt; The problem I have is that some of the dates are in format 05/01/06 and
gt; some are 05/01/2006 so the number of characters for the date is not
gt; consistent (so i cannot just use the Right runction).
gt;
gt; anyone know a way of doing this?
gt;
gt; thanks
gt;
gt;
gt; --
gt; neowok
gt; ------------------------------------------------------------------------
gt; neowok's Profile:
www.excelforum.com/member.php...foamp;userid=5940
gt; View this thread: www.excelforum.com/showthread...hreadid=505307
gt;
=RIGHT(A1,LEN(A1)-FIND(quot; quot;,RIGHT(A1,LEN(A1)-FIND(quot; quot;,A1)))-FIND(quot; quot;,A1))
will give you the date as text

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

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

quot;neowokquot; wrote:

gt;
gt; basically I have a column of name date i.e. quot;John Smith 05/01/06quot;. What
gt; I need to do in a dummy column is put a formula that returns only the
gt; date.
gt;
gt; The problem I have is that some of the dates are in format 05/01/06 and
gt; some are 05/01/2006 so the number of characters for the date is not
gt; consistent (so i cannot just use the Right runction).
gt;
gt; anyone know a way of doing this?
gt;
gt; thanks
gt;
gt;
gt; --
gt; neowok
gt; ------------------------------------------------------------------------
gt; neowok's Profile: www.excelforum.com/member.php...foamp;userid=5940
gt; View this thread: www.excelforum.com/showthread...hreadid=505307
gt;
gt;


thanks thats worked nicely.--
neowok
------------------------------------------------------------------------
neowok's Profile: www.excelforum.com/member.php...foamp;userid=5940
View this thread: www.excelforum.com/showthread...hreadid=505307