将日期时间数据导入Excel [英] Importing date-time data to excel

问题描述

我有一个包含两列的txt文件.这些行如下所示: 20160119,101000

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

意思是"2016年1月19日10:10:00" 将这个日期转换为excel的日期时间数据的最简单方法是什么?

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))

但是我正在寻找的是一种在导出步骤中对其进行格式化的自动方法.我可以想象如下:我创建了自定义的date-yime"yyyymmdd，hhmmss"格式，并告诉Excel识别出我这样导入的数据.

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.

推荐答案

快速

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

我已经将时间留在了B列中.如果您对结果满意，则删除该列应该没有问题.

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

^{在repairDatesYMD处理之前}

^{repairDatesYMD处理后}

这篇关于将日期时间数据导入Excel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！