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

=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

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

hi!

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

where 20060625 housed in A1

-via135


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
------------------------------------------------------------------------
via135's Profile: http://www.excelforum.com/member.php...o&userid=26725
View this thread: http://www.excelforum.com/showthread...hreadid=566780

or

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

VBA Noob


--
VBA Noob
------------------------------------------------------------------------
VBA Noob's Profile: http://www.excelforum.com/member.php...o&userid=33833
View this thread: http://www.excelforum.com/showthread...hreadid=566780

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

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

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

DataText to ColumnsNextNextColumn Data FormatDateYMDFinish


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