Converting Dates to mm/dd/yy format previously imported as text

  • Thread starter Thread starter Terri Reed @SBC
  • Start date Start date
T

Terri Reed @SBC

I have a huge problem. I imported dates into a EXCEL 2002
workbook and it was imported as text in the format 20031119
when I convert it to number and then attempt to format it
as a date format 11/19/03, it does not work. I have 3000
lines of data that I need to convert to the mm/dd/yy
format. Does anyone have any idea how I can do that
quickly without manually going thru and changing the
format?? Any help would be great!
 
Use this formula in a new column:

=TEXT(A1,"00\-00\-00")*1

and format it as mm/dd/yy.

HTH
Jason
Atlanta, GA
 
-----Original Message-----
I have a huge problem. I imported dates into a EXCEL 2002
workbook and it was imported as text in the format 20031119
when I convert it to number and then attempt to format it
as a date format 11/19/03, it does not work. I have 3000
lines of data that I need to convert to the mm/dd/yy
format. Does anyone have any idea how I can do that
quickly without manually going thru and changing the
format?? Any help would be great!
.
I am assuming you know how to put in a macro. Take the
Click to expand...
following sub and put it into a module. Copy and paste
the entire column with the dates into a different sheet.
Then select the top date. This macro will put the
modified dates into the next column. Then past the
corrected dates back into the original sheet.

Thanks,

Greg
 
-----Original Message-----
I have a huge problem. I imported dates into a EXCEL 2002
workbook and it was imported as text in the format 20031119
when I convert it to number and then attempt to format it
as a date format 11/19/03, it does not work. I have 3000
lines of data that I need to convert to the mm/dd/yy
format. Does anyone have any idea how I can do that
quickly without manually going thru and changing the
format?? Any help would be great!
.
Click to expand...
Sorry; I forgot the code. Here it is.

Sub DoDates()

While Not ActiveCell = ""

ActiveCell.Offset(0, 1) = Mid(ActiveCell, 5, 2) & "/"
ActiveCell.Offset(0, 1) = ActiveCell.Offset(0, 1) & Mid
(ActiveCell, 7, 2) & "/"
ActiveCell.Offset(0, 1) = ActiveCell.Offset(0, 1) &
Left(ActiveCell, 4)
ActiveCell.Offset(1, 0).Select
Wend
End Sub
 
Terri

To keep the dates as actual dates which can be used in calculations.

=DATE(VALUE(LEFT(A3,4)), VALUE(MID(A3,5,2)), VALUE(RIGHT(A3,2)))

Entered in an adjacent column and copied down.

Gord Dibben XL2002
 
Jason-- how can I say thanks! This worked like a dream!!
This billboard is a GODSEND! Thanks to everyone who
replied to my post!
 
Back
Top