将“yyyymmdd hhmmss”转换为“mm / dd / yy hh:mm” [英] Convert 'yyyymmdd hhmmss' to 'mm/dd/yy hh:mm'
问题描述
我有一行数据(单元格A3和向下),其中包含一个Unix时间戳,在 yyyymmdd hhmmss
格式中,我想转换为 mm / dd / yy hh:mm
格式自动。
使数据从单元格A1开始,但是我需要A1保持空白,因此数据从A2开始。
Sub auto_open()
'
'auto_open宏
'
'
ChDirC:\
Workbooks.Open文件名:=C:\ Users \username\Desktop\file1.csv
Intersect(Sheets(file1)。Range(A:EW),Sheets(file1)。UsedRange).Copy ThisWorkbook.Sheets Golden)。Range(A2)
Windows(file1.csv)。激活
Application.CutCopyMode = False
ActiveWorkbook.Close SaveChanges = False
Dim x As Integer
Dim y As String
Dim z As String
Dim w As String
NumRows = Range(A3)。End(xlDown ).Row
Windows(ThisWorkbook.Name).Activate
对于x = 2到NumRows
z = Cells(x, 1).Value
y = Mid(z,5,2)& /&中(z,7,2)& /&左(z,4)
w = Mid(z,10,2)& :&中(z,12,2)& :&中间(z,14,2)
y = y + TimeValue(w)
单元格(x,1).Value = y
下一个x
范围( A3)。选择
End Sub
无论我设置范围为A2或A3并向下。有人有建议吗?
调试亮点 y = Mid(z,5,2)& /&中(z,7,2)& /&左(z,4)
但我不知道是什么问题。
格式化单元格(custom> mm / dd / yyyy hh:mm:ss
)在我的情况下也不行,不幸的是,
这样做不会有循环:
Sub kyle()
带有[a3]。调整大小([a1048576] .End(xlUp).Row - 2)
.Value =评估(转置(转置(DATE(&.Address&,1,4),MID & .Address&,5,2),MID(&地址,7,2))+ TIME(MID(&地址,10,2) & .Address&,12,2),MID(& .Address&,14,2))))))
结束
End Sub
注意:您可以随时使用任何数字格式设置日期。
I have a row of data (Cell A3 and down) that contains a Unix timestamp in yyyymmdd hhmmss
format that I'm trying to convert to mm/dd/yy hh:mm
format automatically.
The code I have so far works when I have the data starting in cell A1, but I need A1 to remain blank, therefore the data starts at A2. Here is an example screenshot of column A:
Sub auto_open()
'
' auto_open Macro
'
'
ChDir "C:\"
Workbooks.Open Filename:="C:\Users\username\Desktop\file1.csv"
Intersect(Sheets("file1").Range("A:EW"), Sheets("file1").UsedRange).Copy ThisWorkbook.Sheets("Golden").Range("A2")
Windows("file1.csv").Activate
Application.CutCopyMode = False
ActiveWorkbook.Close SaveChanges = False
Dim x As Integer
Dim y As String
Dim z As String
Dim w As String
NumRows = Range("A3").End(xlDown).Row
Windows(ThisWorkbook.Name).Activate
For x = 2 To NumRows
z = Cells(x, 1).Value
y = Mid(z, 5, 2) & "/" & Mid(z, 7, 2) & "/" & Left(z, 4)
w = Mid(z, 10, 2) & ":" & Mid(z, 12, 2) & ":" & Mid(z, 14, 2)
y = y + TimeValue(w)
Cells(x, 1).Value = y
Next x
Range("A3").Select
End Sub
It errors whether I set the range to A2 or A3 and down.
Does anyone have recommendations?
Debug highlights y = Mid(z, 5, 2) & "/" & Mid(z, 7, 2) & "/" & Left(z, 4)
but I'm not sure what the problem is.
Format cells (custom > mm/dd/yyyy hh:mm:ss
) also does not work in my case, unfortunately.
This will do it with no looping at all:
Sub kyle()
With [a3].Resize([a1048576].End(xlUp).Row - 2)
.Value = Evaluate("transpose(transpose(DATE(MID(" & .Address & ",1,4),MID(" & .Address & ",5,2),MID(" & .Address & ",7,2)) + TIME(MID(" & .Address & ",10,2),MID(" & .Address & ",12,2),MID(" & .Address & ",14,2))))")
End With
End Sub
Note: you can then use whatever number formatting for the dates you please.
这篇关于将“yyyymmdd hhmmss”转换为“mm / dd / yy hh:mm”的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!