Macro to split data and time into two columns

[abberyfarm]

hi there,

I have dates and times in one column like this "12/08/2011 11:24", would anyone have a macro to split them into two columns?

 

[John_w]

You could use formulae:

=TEXT(A1,"DD/MM/YYYY")
=TEXT(A1,"HH:MM")

 

[GTO]

I wasn't sure if yyou wanted it offset or in-place. Just to try on a junk copy of your wb, select the cells and run:

Option Explicit
    
Sub SplitDateTime()
Dim Cell As Range
    
    For Each Cell In Selection
        If IsDate(Cell.Value) Then
            Cell.Offset(, 1).Resize(, 2).Value _
                = Array(DateSerial(Year(Cell.Value), Month(Cell.Value), Day(Cell.Value)), _
                        TimeSerial(Hour(Cell.Value), Minute(Cell.Value), Second(Cell.Value)))
        End If
    Next
End Sub

To get:

Excel Workbook
	E	F	G
1	Before	After	After
2	9/18/2011 13:24	9/18/2011	13:24
3	9/19/2011 14:00	9/19/2011	14:00
4	9/20/2011 7:00	9/20/2011	07:00
5	10/10/2010 23:53	10/10/2010	23:53
Sheet4

 

[abberyfarm]

Thanks guys, both options worked fine!