[SOLVED] Converting number or text to a Date Format

Hi there,

First post.....

I downloaded a file from my database (not access) which outputs the
file to .txt

Using excel I can set each colum for the text and it puts each piece of
data in to a column etc. Very nice.

One of the pieces of data I output from the system is a persons date of
birth, however, it is shown as (an example) 19340101. I can convert this
to something that looks nicer by using
=RIGHT(A1,2)&"/"&MID(A1,5,2)&"/"&LEFT(A1,4) but that doesn't convert
the number in to a date format.

I need to convert 19340101 to 01/01/1934 in a date format that excel
can understand.

Any ideas.

Thanks in advance.

Stewart


--
samhain
------------------------------------------------------------------------
samhain's Profile: http://www.hightechtalks.com/m92
View this thread: http://www.hightechtalks.com/t2262615

Try a formula of

=Date(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))

--

HTH

RP
(remove nothere from the email address if mailing direct)


"samhain" <[email protected]> wrote in message
news:[email protected]...
>
> Hi there,
>
> First post.....
>
> I downloaded a file from my database (not access) which outputs the
> file to .txt
>
> Using excel I can set each colum for the text and it puts each piece of
> data in to a column etc. Very nice.
>
> One of the pieces of data I output from the system is a persons date of
> birth, however, it is shown as (an example) 19340101. I can convert this
> to something that looks nicer by using
> =RIGHT(A1,2)&"/"&MID(A1,5,2)&"/"&LEFT(A1,4) but that doesn't convert
> the number in to a date format.
>
> I need to convert 19340101 to 01/01/1934 in a date format that excel
> can understand.
>
> Any ideas.
>
> Thanks in advance.
>
> Stewart
>
>
> --
> samhain
> ------------------------------------------------------------------------
> samhain's Profile: http://www.hightechtalks.com/m92
> View this thread: http://www.hightechtalks.com/t2262615
>

Another way:
select that column of "dates"
data|text to columns
fixed width -- but don't subdivide that field
choose ymd (ydm???) as the field type
format the way you want.



samhain wrote:
>
> Hi there,
>
> First post.....
>
> I downloaded a file from my database (not access) which outputs the
> file to .txt
>
> Using excel I can set each colum for the text and it puts each piece of
> data in to a column etc. Very nice.
>
> One of the pieces of data I output from the system is a persons date of
> birth, however, it is shown as (an example) 19340101. I can convert this
> to something that looks nicer by using
> =RIGHT(A1,2)&"/"&MID(A1,5,2)&"/"&LEFT(A1,4) but that doesn't convert
> the number in to a date format.
>
> I need to convert 19340101 to 01/01/1934 in a date format that excel
> can understand.
>
> Any ideas.
>
> Thanks in advance.
>
> Stewart
>
> --
> samhain
> ------------------------------------------------------------------------
> samhain's Profile: http://www.hightechtalks.com/m92
> View this thread: http://www.hightechtalks.com/t2262615

--

Dave Peterson