Conversion of date into different format

  • Thread starter Fam via OfficeKB.com
  • Start date
F

Fam via OfficeKB.com

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

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)
 
J

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!
 
V

via135

hi!

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

where 20060625 housed in A1

-via135
 
F

Fam via OfficeKB.com

Thanks guys

jb_tenor1 said:
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!
Click to expand...
 
H

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
 
G

Gord Dibben

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


Gord Dibben MS Excel MVP
 
Top