问题描述
我有一个包含两列的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.
推荐答案
快速 Range.TextToColumns方法可以正确地从YMD转换A列中的日期,但是需要一点一点的操作就可以增加B列中的时间.
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.
这篇关于将日期时间数据导入Excel的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!