Conversion of date into different format

[Fam via OfficeKB.com]

can date 20060625 be converted into 06/25/2006?
Any help will be appreciated.
Thx

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200607/1

[Bob Phillips]

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

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Fam via OfficeKB.com" <u18245@uwe> wrote in message
news:641510bb9fb49@uwe...
> can date 20060625 be converted into 06/25/2006?
> Any help will be appreciated.
> Thx
>
> --
> Message posted via OfficeKB.com
> http://www.officekb.com/Uwe/Forums.a...excel/200607/1
>

[jb_tenor1]

Yes, if all of your data is like this in the list, there's a little bit of
work you need to do:

First, insert three columns next to your date column and do "Data> Text to
Columns" using Fixed Width, and put your lines after 2006 and 06 so that the
data is divided like this: 2006|06|25

Then in the blank cell next to your separated data, concatenate the
separated cells back together so that they dispay as 06/25/2006.

Format this column as a Date in the format you want it to display.

Finally, do a Text to Columns again, use Delimited, but make sure to remove
any checkmarks from the delimiters before clicking finish. This will
finalize the field as a date and fix your problem.

Yes, I've had to do this a couple of times myself.

Let me know if you have any problems!

"Fam via OfficeKB.com" wrote:

> can date 20060625 be converted into 06/25/2006?
> Any help will be appreciated.
> Thx
>
> --
> Message posted via OfficeKB.com
> http://www.officekb.com/Uwe/Forums.a...excel/200607/1
>
>

[via135]

hi!

=TEXT(MID(A1,5,2)&"/"&RIGHT(A1,2)&"/"&LEFT(A1,4),"mm/dd/yyyy")

where 20060625 housed in A1

-via135

can date 20060625 be converted into 06/25/2006?
Any help will be appreciated.
Thx

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200607/1

[VBA Noob]

or

=MID(A1,5,2)&"/"&RIGHT(A1,2)&"/"&LEFT(A1,4)

VBA Noob

[Fam via OfficeKB.com]

Thanks guys

jb_tenor1 wrote:
>Yes, if all of your data is like this in the list, there's a little bit of
>work you need to do:
>
>First, insert three columns next to your date column and do "Data> Text to
>Columns" using Fixed Width, and put your lines after 2006 and 06 so that the
>data is divided like this: 2006|06|25
>
>Then in the blank cell next to your separated data, concatenate the
>separated cells back together so that they dispay as 06/25/2006.
>
>Format this column as a Date in the format you want it to display.
>
>Finally, do a Text to Columns again, use Delimited, but make sure to remove
>any checkmarks from the delimiters before clicking finish. This will
>finalize the field as a date and fix your problem.
>
>Yes, I've had to do this a couple of times myself.
>
>Let me know if you have any problems!
>
>> can date 20060625 be converted into 06/25/2006?
>> Any help will be appreciated.
>> Thx

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200607/1

[Harald Staff]

20060625 is not a date, it is a big number, 20 million something. The date
in question is a smaller number, 38893. See Chip's page
http://www.cpearson.com/excel/datetime.htm
on why this is so. To change 20 million some to 38 thousand some you can use
a formula in a neighbor cell,
=DATE(INT(A1/10000),MOD(INT(A1/100),100),MOD(A1,100))
,or with a macro that runs on entry, see another one of Chip's pages for
this
http://www.cpearson.com/excel/DateTimeEntry.htm

HTH. Best wishes Harald

"Fam via OfficeKB.com" <u18245@uwe> skrev i melding
news:641510bb9fb49@uwe...
> can date 20060625 be converted into 06/25/2006?
> Any help will be appreciated.
> Thx
>
> --
> Message posted via OfficeKB.com
> http://www.officekb.com/Uwe/Forums.a...excel/200607/1
>

[Dave Peterson]

One more formula...
=--text(a1,"0000\/00\/00")
Format as a date.



"Fam via OfficeKB.com" wrote:
>
> can date 20060625 be converted into 06/25/2006?
> Any help will be appreciated.
> Thx
>
> --
> Message posted via OfficeKB.com
> http://www.officekb.com/Uwe/Forums.a...excel/200607/1

--

Dave Peterson

[Gord Dibben]

Data>Text to Columns>Next>Next>Column Data Format>Date>YMD>Finish


Gord Dibben MS Excel MVP

On Mon, 31 Jul 2006 19:23:40 GMT, "Fam via OfficeKB.com" <u18245@uwe> wrote:

>can date 20060625 be converted into 06/25/2006?
>Any help will be appreciated.
>Thx