software

I have a field in a delimited CSV. file 20060229 and want to convert it to a
date field reading 29/02/2006. How can I do that?

There are only 28 days in February in 2006. You should get March 1 as
1/3/2006 using =DATE(LEFT(A1,4),MID(A1,5,2),(RIGHT(A1,2)))
--
Gary's Studentquot;DDquot; wrote:

gt; I have a field in a delimited CSV. file 20060229 and want to convert it to a
gt; date field reading 29/02/2006. How can I do that?

After you import the .csv file, you can select that field and use data|text to
columns.

You can specify ymd as the field type.

DD wrote:
gt;
gt; I have a field in a delimited CSV. file 20060229 and want to convert it to a
gt; date field reading 29/02/2006. How can I do that?

--

Dave Peterson

You can only make it TEXT to appear to be 29/02/2006.

=RIGHT(A1,2)amp;quot;/quot;amp;MID(A1,5,2)amp;quot;/quot;amp;LEFT(A1,4)

If turned into a DATE it becomes 3/1/3006 as there is no Feb 29th in
2006.....

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

Vaya con Dios,
Chuck, CABGx3
quot;DDquot; gt; wrote in message
...
gt; I have a field in a delimited CSV. file 20060229 and want to convert it to
a
gt; date field reading 29/02/2006. How can I do that?

You can't because there's no such date as 29/02/2006 but if you have a
real date e.g. 20060228 you can either...

..use Data gt; Text to Columns and at step three choose date option and
YMD.

or with a formula, assuming your quot;datequot; in A1

=DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: www.excelforum.com/member.php...oamp;userid=30486
View this thread: www.excelforum.com/showthread...hreadid=517561Sorry, 3/1/2006 not 3/1/3006....quot;you know what I meantquot;....the formula is
ok, just mine typing is tired.......bed time here in St. Petersburg,
Florida......

Vaya con Dios,
Chuck, CABGx3

quot;CLRquot; gt; wrote in message
...
gt; You can only make it TEXT to appear to be 29/02/2006.
gt;
gt; =RIGHT(A1,2)amp;quot;/quot;amp;MID(A1,5,2)amp;quot;/quot;amp;LEFT(A1,4)
gt;
gt; If turned into a DATE it becomes 3/1/3006 as there is no Feb 29th in
gt; 2006.....
gt;
gt; =DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))
gt;
gt; Vaya con Dios,
gt; Chuck, CABGx3
gt;
gt;
gt;
gt; quot;DDquot; gt; wrote in message
gt; ...
gt; gt; I have a field in a delimited CSV. file 20060229 and want to convert it
to
gt; a
gt; gt; date field reading 29/02/2006. How can I do that?
gt;
gt;