Importing date-time data to excel

问题:

I have a txt file containing two columns. The rows look like this: 20160119,101000

Which means "19 January 2016 10:10:00" What is the easiest way to convert this date to excel's date-time data?

I.e. eventually I want to get one column with date-time values.

ADD: Of cause, I can do this:

DATEVALUE(MID(A11;5;2)  & "/" &MID(A11;7;2) & "/" & MID(A11;1;4)) + TIMEVALUE(MID(A11;10;2) & ":" & MID(A11;12;2) & ":" & MID(A11;14;2))

But what I'm looking for is some automatic way to format it on the very export step. I can imagine it as follows: I create custom date-yime "yyyymmdd, hhmmss" format and tell Excel to recognize the data I import as such.

回答1:

One way to do this is to open a blank Excel file and use Data, From Text to import the textfile. Use the comma as the delimiter. Make sure you set both columns as text in the third step of the text import wizard to avoid loosing leading zeroes. See jkp-ads.com/articles/importtext.asp for some pointers on this process.

You should get 20160119 in column A and 101000 in column B.

Then convert those values in columns A and B into dates and times using these two formulas:

=DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))

and

=TIMEVALUE(LEFT(B1,2)&":"&MID(B1,3,2)&":"&RIGHT(B1,2))

Note that this may have to be adjusted for time values less than 10:00:00 if they appear as 90000 for 9:00:00, rather than as 090000. In that case, use this formula for the time:

=TIMEVALUE(LEFT(B1,IF(LEN(B1)=5,1,2))&":"&MID(B1,3,2)&":"&RIGHT(B1,2))

回答2:

A quick Range.TextToColumns method can convert the date in column A from YMD correctly but a little cell-by-cell manipulation would be necessary to add the times from column B.

Sub repairDatesYMD()
    Dim rw As Long

    With Worksheets("sheet2")
        With .Cells(1, 1).CurrentRegion
            .Columns(1).TextToColumns Destination:=.Cells(1), DataType:=xlFixedWidth, _
                                      FieldInfo:=Array(0, 5)
            For rw = 2 To .Cells(Rows.Count, 2).End(xlUp).Row
                .Cells(rw, 1) = .Cells(rw, 1).Value2 + _
                    TimeValue(Left(.Cells(rw, 2).Text, 2) & Chr(58) & _
                              Mid(.Cells(rw, 2).Text, 3, 2) & Chr(58) & _
                              Right(.Cells(rw, 2).Text, 2))

            Next rw
            .Range(.Cells(rw - 1, 1), .Cells(rw - 1, 1).End(xlUp)).NumberFormat = _
                "dd mmmm yyyyy hh:mm:ss"
        End With
    End With
End Sub

I have left the times in column B. If you are satisfied with the results, there should be no problem deleting the column.

        
                ^{Before repairDatesYMD processing}

        
                ^{After repairDatesYMD processing}