Time

[DD]

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?

[daddylonglegs]

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 > Text to Columns and at step three choose date option and YMD.

or with a formula, assuming your "date" in A1

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

[Gary''s Student]

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 Student


"DD" wrote:

> 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?

[Dave Peterson]

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:
>
> 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?

--

Dave Peterson

[CLR]

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

=RIGHT(A1,2)&"/"&MID(A1,5,2)&"/"&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



"DD" <[email protected]> wrote in message
news:[email protected]...
> 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?

[CLR]

Sorry, 3/1/2006 not 3/1/3006...."you know what I meant"....the formula is
ok, just mine typing is tired.......bed time here in St. Petersburg,
Florida......

Vaya con Dios,
Chuck, CABGx3




"CLR" <[email protected]> wrote in message
news:[email protected]...
> You can only make it TEXT to appear to be 29/02/2006.
>
> =RIGHT(A1,2)&"/"&MID(A1,5,2)&"/"&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
>
>
>
> "DD" <[email protected]> wrote in message
> news:[email protected]...
> > 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?
>
>