Solved: date conversion macro

[asddsa88]

Hello, I have an excel spreadsheet that I need to import into another...

when I import the data, both the numbers (with decimals) and dates (stored in the format 01-01-2007 for example) GET STORED AS TEXT.

The "number stored as text" problem is very common and I solved it in this way: ActiveSheet.Range("f2", ActiveSheet.Range("f2").End(xlDown)).Select


Dim cell As Range

For Each cell In Selection
If Not IsEmpty(cell) And IsNumeric(cell.Value) Then
cell.Value = CDbl(cell.Value)
End If
Next cell

now I need to convert the dates stored as general text into a date format.
Note: right clicking on the dates and setting the date format doesn't work, only thing that works is double clicking on a single cell and the date magically becomes 01/01/2007 format which is what I want..

now I need a macro that does this job for me...

help appreciated!!

[asddsa88]

just found a workaround, multiply all the dates * 1 ... I get the serial numbers and then the format / date works...

just curious if there is a vba function to do this!


**** edit, doesn't work unless I double click on the date cell, so back to square 1.. help please!

[Bob Phillips]

Have you a workbook for us to work with?

[asddsa88]

sure!

[asddsa88]

problem is now COMPLETELY NONSENSE

using the sheet I attached in the other post, try this code!

only SOME random dates get fixed, while some others simply decide to stay the old format... absolutely nonsense!!

:banghead::banghead:

Range("b1").Select

Selection.Copy
Range("A1:A100").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlAdd, SkipBlanks:= _
False, Transpose:=False



Application.CutCopyMode = False
Selection.NumberFormat = "m/d/yyyy"

[Bob Phillips]

How about this?



Columns(1).TextToColumns Destination:=Range("A1"), _
FieldInfo:=Array(1, 4)

[asddsa88]

How about this?



Columns(1).TextToColumns Destination:=Range("A1"), _
FieldInfo:=Array(1, 4)


same as my code, only SOME get fixed while half of them stays in the old way.. :dunno

[asddsa88]

solved!

selected all the cells with the date field, copy, paste special (add) onto blank cells range!

works!

thanks anyway for the help!