Using VBA to Perform Text To Columns, While Preserving Date and Time Formatting

[reberryjr]

I need to take several fields with date/time formatting like this, and separate the time from the date. I've been formatting the column as MM/DD/YY HH:MM, which appears fine, but the value in the formula bar doesn't change. So, when I do text to columns, suing SPACE as the delimiter, I lose the 24 hour aspect. I even try to reformat the new Time column as HH:MM, but it doesn't hold.


Thoughts?

Incoming Data	What I Want
6/13/2018 3:52 PM	06/13/18	15:52

<tbody>
</tbody>

 

[reberryjr]

Nevermind. I was able to piece it together.

'Performs Text to Columns, separating the Date from the Time, and formats the Date as MM/DD/YY.
With sws.Range("D2:D" & slR)
    .TextToColumns Destination:=sws.Range("D2"), _
    DataType:=xlFixedWidth, _
    FieldInfo:=Array(Array(0, 3), Array(10, 1)), TrailingMinusNumbers:=True
    .NumberFormat = "mm/dd/yy;@"
End With
'Formats the Time as 24 Hour.
With sws.Range("E2:E" & slR)
    .NumberFormat = "hh:mm"
End With