spilt date and time to new columns

[anish.ms]

Hi
Could you please help me with the following code to split the date and time from selection to two separate adjutant columns?
The code is working fine but I know that there a room for improvement instead of duplicating the same code. But I'm not much sure of it using the arrays.
Thanks, in advance.


Sub DateTime()
Dim myVals As Variant, y As Variant
Dim i As Long


myVals = Selection.Value2
On Error Resume Next
For i = 1 To UBound(myVals)
y = Split(myVals(i, 1))
myVals(i, 1) = Format(y(0), "dd/mmm/yyyy")
Next i
ActiveCell.EntireColumn.Offset(0, 1).Insert
Selection.Offset(, 1).Value = myVals
Erase myVals


myVals = Selection.Value2
On Error Resume Next
For i = 1 To UBound(myVals)
y = Split(myVals(i, 1))
myVals(i, 1) = Format(y(1), "hh:mm AM/PM")
Next i
ActiveCell.EntireColumn.Offset(0, 2).Insert
Selection.Offset(, 2).Value = myVals


End Sub

[Logit]

31372

[salmonstray]

Hi! this works for me, how do u like it?


Sub DateTime2()
Dim myVals As Variant
Dim i As Long
Dim Datestr As String, Timestr As String
Columns(ActiveCell.Column + 1).Resize(, 2).Insert
'2 inserted columns' format-> string
Selection.Offset(0, 1).Resize(, 2).NumberFormatLocal = "＠" 'need to change this ＠ to a normal one(I changed this due to post error)
myVals = Selection.Value2
For i = 1 To UBound(myVals)
Datestr = Format(myVals(i, 1), "dd/mmm/yyyy")
Timestr = Format(myVals(i, 1), "hh:mm AM/PM")
Selection.Item(i).Offset(0, 1) = Datestr 'the selection has to be 1 column
Selection.Item(i).Offset(0, 2) = Timestr 'the selection has to be 1 column
Next i
End Sub

[arnelgp]

you can just copy the cells to new cells (two cells for date and time).
Change the Format of the first cell to Date and the other as Time.

[anish.ms]

Thanks Logit for the formula. but I was looking for the vba code to be part of the sub

[anish.ms]

Thanks, this works

[anish.ms]

But this will change only the display format
I was looking for a code to be part of the sub

[Logit]

Sub SplitLongDate()Dim D As Range, i As Long
Dim H As Integer, M As Integer, S As Integer


For i = 2 To Range("A" & Rows.Count).End(xlUp).Row
Set D = Range("A" & i)
If D = "" Then Exit Sub
D.Offset(0, 1).Resize(1, 2) = Split(D, " ")
H = Hour(D): M = Minute(D): S = Second(D)
D.Offset(0, 2) = H & ":" & M & ":" & S
D.Offset(0, 2).NumberFormat = "hh:mm:ss"
Next i

End Sub

[arnelgp]

in reality, date/time has Always the Date portion. if you strip the date portion, still it has the date on it (even day 1).
Logit has code, but see, he is also formatting it to appear as if there is No Date part.

[anish.ms]

Thanks Logit
But the array formula in my original post works faster than the looping of each cell.
I was actually looking for a guidance to simplify the code instead of duplicating the code the way i have written

[Logit]

Ok

[snb]

Sub M_snb()
selection.TextToColumns , , , , 0, 0, 0, 1
End Sub