Convert 'yyyymmdd hhmmss' to 'mm/dd/yy

问题:

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.

回答1:

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.

回答2:

Try this:

Sub Kyle()
  Dim cell As Range

  ThisWorkbook.Activate

  For Each cell In Range("A2", Cells(Rows.Count, "A").End(xlUp))
    If cell.Value Like "######## ######" Then
      cell.Value = CDate(Format(cell.Value, "@@@@-@@-@@@@@:@@:@@"))
    End If
  Next cell
End Sub

Then format the column however you prefer.

For me, that converts

20150904 213613
20150124 194003
20150404 163056
20151220 100509
20150510 213512

to this:

09/04/2015 21:36
01/24/2015 19:40
04/04/2015 16:30
12/20/2015 10:05
05/10/2015 21:35

回答3:

@ExcelHero answered my question via email. The following is the working code for anyone who needs future reference.

    With [a3].Resize([a65536].End(xlUp).Row - 2)
    If Len(.Item(1)) = 15 Then
        .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 If
    .NumberFormat = "mm/dd/yyyy hh:mm"
End With