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?
[TABLE="class: grid, width: 300"]
<tbody>[TR]
[TD]Incoming Data[/TD]
[TD]What I Want[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6/13/2018 3:52 PM[/TD]
[TD]06/13/18[/TD]
[TD]15:52[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 128"]
[/TABLE]

 

[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