separating date and time

[wabbleknee]

Greetings

I have some formatted data that looks like this:
A
1 04/01/2013 12:00:00 PM
2 04/03/2013 09:00:00 AM
3 04/03/2013 01:45:00 PM

I would like to have the resulting data to look like this

A B
1 04/01/2013 12:00
2 04/03/2013 09:00
3 04/03/2013 13:45

Date in separate column with no time stamp remaining
Time in a separate column with no date stamp remaining
Time is 24 hr format.

I can format A1 04/01/2013 12:00:00 PM with mm/dd/yyyy
the cell will show 04/01/2013 but 12:00:00 still remains in the cell.
I want all time references removed. B1 only time.

Any help appreciated. Tx

 

[Claus Busch]

Hi,

Am Wed, 24 Apr 2013 16:38:02 -0400 schrieb wabbleknee:

A
1 04/01/2013 12:00:00 PM
2 04/03/2013 09:00:00 AM
3 04/03/2013 01:45:00 PM

I would like to have the resulting data to look like this

A B
1 04/01/2013 12:00
2 04/03/2013 09:00
3 04/03/2013 13:45

select column A => TextToColumns => Delimited => Delimiter is Blank


Regards
Claus Busch

 

[wabbleknee]

Tx Claus, here are my results, testing one cell:

A3 = 04/03/2013 01:45:00 PM
A3 is selected, TextToColumns
Delimited is selected
Selected data shows 4/3/2013 01:45:00 PM
Next, , All Delimiters are not selected (blank)
data preview = 4/3/2013 01:45:00 PM
Next, general format, destination B1
B1 = 4/3/2013 13:45 in same cell
The fx(function) window shows 4/3/2013 1:45:00 PM

Cannot get them separated. Tx for additional help





"Claus Busch" wrote in message
Hi,

Am Wed, 24 Apr 2013 16:38:02 -0400 schrieb wabbleknee:

A
1 04/01/2013 12:00:00 PM
2 04/03/2013 09:00:00 AM
3 04/03/2013 01:45:00 PM

I would like to have the resulting data to look like this

A B
1 04/01/2013 12:00
2 04/03/2013 09:00
3 04/03/2013 13:45

select column A => TextToColumns => Delimited => Delimiter is Blank


Regards
Claus Busch

 

[Claus Busch]

Hi again,

Am Wed, 24 Apr 2013 17:23:14 -0400 schrieb wabbleknee:

A3 = 04/03/2013 01:45:00 PM
A3 is selected, TextToColumns
Delimited is selected
Selected data shows 4/3/2013 01:45:00 PM
Next, , All Delimiters are not selected (blank)
data preview = 4/3/2013 01:45:00 PM
Next, general format, destination B1
B1 = 4/3/2013 13:45 in same cell
The fx(function) window shows 4/3/2013 1:45:00 PM

first use custon format for the cells:
dd/mm/yyyy hh:mm
Then click in column header to select the column => TextToColumns =>
Delimited => Delimiter is Blank => Format for the first colum is Date
d/m/y, format for the second cell is "General" => Destination is A3 (if
your values begin in A3) => Finish
If you now have in column A a date with time 00:00 format the column for
date.
Or try this macro:

Sub SeparateData()
Dim LRow As Long

LRow = Cells(Rows.Count, 1).End(xlUp).Row
With Range("A1:A" & LRow)
.NumberFormat = "mm\/dd\/yyyy hh:mm"
.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, _
Space:=True, Other:=False, FieldInfo _
:=Array(Array(1, 3), Array(2, 1)), TrailingMinusNumbers:=True
.NumberFormat = "mm\/dd\/yyyy"
End With
End Sub


Regards
Claus Busch

 

[Claus Busch]

Hi,

Am Wed, 24 Apr 2013 17:23:14 -0400 schrieb wabbleknee:

Cannot get them separated.

if you can't get my suggestions to work, you can also try it with
formula
For the date in B1:
=INT(A1)
For the time in C1:
=MOD(A1,1)
Then set the format of column A and B as you want it.
Copy column A and B and paste special => Paste values.
Then you can delete column A


Regards
Claus Busch

 

[wabbleknee]

Tx again Claus.... I have a solution that works thanks to you!
The data comes to me in the mm/dd/yyyy format (April 3, 2013) so I was able
to change that around.
I notice however that the macro format matches my data!
My work project is to identify peak work load by day of week and hour of
each day using the time stamps in question.

Mike

"Claus Busch" wrote in message
Hi again,

Am Wed, 24 Apr 2013 17:23:14 -0400 schrieb wabbleknee:

A3 = 04/03/2013 01:45:00 PM
A3 is selected, TextToColumns
Delimited is selected
Selected data shows 4/3/2013 01:45:00 PM
Next, , All Delimiters are not selected (blank)
data preview = 4/3/2013 01:45:00 PM
Next, general format, destination B1
B1 = 4/3/2013 13:45 in same cell
The fx(function) window shows 4/3/2013 1:45:00 PM

first use custon format for the cells:
dd/mm/yyyy hh:mm
Then click in column header to select the column => TextToColumns =>
Delimited => Delimiter is Blank => Format for the first colum is Date
d/m/y, format for the second cell is "General" => Destination is A3 (if
your values begin in A3) => Finish
If you now have in column A a date with time 00:00 format the column for
date.
Or try this macro:

Sub SeparateData()
Dim LRow As Long

LRow = Cells(Rows.Count, 1).End(xlUp).Row
With Range("A1:A" & LRow)
.NumberFormat = "mm\/dd\/yyyy hh:mm"
.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, _
Space:=True, Other:=False, FieldInfo _
:=Array(Array(1, 3), Array(2, 1)), TrailingMinusNumbers:=True
.NumberFormat = "mm\/dd\/yyyy"
End With
End Sub


Regards
Claus Busch