Solved: Format csv Date to xls Date format problems

[guatelize]

Hello
I'm importing every day with a macro a csv file into excel, containing dates.

I'm looking for a macro to find between ranges A:Z in the imported data the format dd.mm.yyyy and convert it to the datevalue :

03.01.2011
= 40546

Is this possible ?:help

Thanks for your help

[Kenneth Hobs]

I am not sure why you would not do it in the first macro. Doing it for A:Z is overkill.

Dim cell As Range
For Each cell In ActiveSheet.UsedRange
If cell.NumberFormat = "mm.dd.yyyy" Then cell.NumberFormat = "General"
Next cell

[guatelize]

Thanks for your help, but not working, sending you a file with an example

Col A has text format, I wish to have it replaced by datevalue

Thanks

[guatelize]

I've found this macro, but can't have it work :

Private Sub Worksheet_Change(ByVal Target As Range)
Select Case Target.Column
Case 3, 5 ' columns C & E are 3rd & 5th columns
TypedVal = Application.WorksheetFunction. _
Text(Target.Text, "00.00.0000")
NewValue = Left(TypedVal, 2) & "/" & _
Mid(TypedVal, 3, 2) & "/" & _
Right(TypedVal, 2)
Case 4, 6 ' Columns D & F are time columns
TypedVal = Application.WorksheetFunction. _
Text(Target.Value, "00/00/0000")
NewValue = Left(TypedVal, 2) & "/" & _
Right(TypedVal, 2)
End Select
If NewValue > 0 Then
Application.EnableEvents = False
Target.Value = NewValue
Application.EnableEvents = True
End If
End Sub

[Kenneth Hobs]

Then it is not date formatted but simply a string value.

Sub Button1_Click()
Dim cell As Range, a() As String
For Each cell In ActiveSheet.UsedRange
If Len(cell.Value2) = 10 Then
a() = Split(cell.Value2, ".")
If UBound(a) = 2 Then
cell.Value2 = DateSerial(a(2), a(0), a(1))
End If
End If
Next cell
End Sub

[guatelize]

Works perfect, the only problem I have now is that it gives me the US date format : mm/dd/yyyy instead of the Europe format dd/mm/yyyy
How can I change the macro ?
Thanks for your service

[Kenneth Hobs]

That is the trouble with string dates.

cell.Value2 = DateSerial(a(2), a(1), a(0))

[guatelize]

This is wonderfull, you save me 1 hour work every day. Now I can extract correct dates with formulas into my pivot tables. If you have any information or websites about importing csv (delimited) into excel, always welcome.
Have a wonderful day and thank very much

[Kenneth Hobs]

There are several ways to do the import of csv data. Some odd things have to be addressed such as your date issue so it is probably best to ask for help. Post a new thread with a zipped file of a very short csv file and an example xlsm of what it should look like after importing.

[guatelize]

Thanks for your help. I will do so.

[guatelize]

http://www.vbaexpress.com/forum/showthread.php?t=37475