CODEWITHSUNDEEP
exceldate-conversionvba
Janthelme
Janthelme

Reputation: 999

TextToColumns for date strings with time

Excel 2010. I am importing csv files within excel for further processing. Unfortunately in the csv date fields, dates are expressed in a format ("dd/mm/yyyy") different from my PC's default format ("mm/dd/yyyy"). So I need to convert each date field (column) to my format before I can start to work on the data.

The Range.TextToColumns method works like a charm :

Sub convertDateColumn()
    Dim myColumn As Range

    Set myColumn = Range("A1:A10")
    myColumn.TextToColumns Destination:=myColumn.Cells(1), DataType:=xlDelimited, FieldInfo:=Array(0, xlDMYFormat)

End Sub

(assuming that the date strings are in the column range A1:A10)

... which properly converts the string "1/2/2017" into the date 1-Feb-2017 or the string "25/2/2017" into the date 25-Feb-2017.

However, some fields are not pure date strings, but contain also time information. Eg "1/2/2017 09:30:00" for 9:30am on 1-Feb-2017 or "25/2/2017 09:30:00" for 9:30am on 25-Feb-2017.

And unfortunately the TextToColumns method as used in the example macro above does not seem to work any longer. "1/2/2017 09:30:00" is incorrectly converted to 9:30am on 2-Jan-2017 and "25/2/2017 09:30:00" is not converted.This is normal in a way since the field info (xlDMYFormat) no longer describes the field format accurately.

My question: is there a way to use TextToColumns with strings containing time information? Alternatively are there other ways to convert all the column's date/time strings without looping through each cell (which is easy but slow)?

Upvotes: 2

Views: 1134

Answers (2)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60224

To use TTC with time format, you need to split off the Time (delimiter = space). Will that work with your data?

You will also need a method of handling your string dates or date/times.

Easiest would be to do a real Import (Data ► Get External Data ► From Text) and process the data correctly before it gets written to the worksheet. That would require being able to also use Space as a delimiter.

enter image description here

enter image description here

enter image description here

enter image description here

enter image description here

Note that the dates in the last screen shot have been converted to my "normal" settings of MDY, so they all represent February dates.

If that does not work, let me know as I have a macro which should be able to convert mixed date/times in mixed formats.

Upvotes: 2

Robin Mackenzie
Robin Mackenzie

Reputation: 19289

Maybe you can just bring the datetime field in as xlGeneralFormat and then format the first column after the TextToColumns has processed with:

myColumn.NumberFormat = "mm/dd/yyyy hh:mm:ss"

For example:

Sub convertDateColumn()
    Dim myColumn As Range

    Set myColumn = Range("A1:A10")
    myColumn.TextToColumns Destination:=myColumn.Cells(1), DataType:=xlDelimited, FieldInfo:=Array(0, xlGeneralFormat)

    ' update format
    myColumn.NumberFormat = "mm/dd/yyyy hh:mm:ss"

End Sub

Upvotes: 2

Related Questions