Help with Macro to Convert Date/Time to Time Only
 

This is what I have, I have a group of cell that look like this:
5/24/2004 14:20
5/12/2004 10:22
5/4/2004 13:54
5/11/2004 16:37
5/24/2004 10:09
5/15/2004 12:05
5/3/2004 12:37
5/24/2004 11:45
5/11/2004 11:40
5/18/2004 14:05
5/15/2004 11:45
5/24/2004 13:48
5/14/2004 17:03
5/11/2004 16:26
5/24/2004 14:51

I would like a macro to edit each cell and delete the date and keep
the time. I would like to do the opposite, and have another column
with date and time and delete the time and keep the date. I have no
problem with making new columns and copy the same data twice, but need
to have one column with pure date and one with pure time.

I have never done a macro in Excel and as you can see this is all very
new to me.

Thanks,

Help with Macro to Convert Date/Time to Time Only
 

"Bill" wrote in message
om...

This is what I have, I have a group of cell that look like this:
5/24/2004 14:20
5/12/2004 10:22
5/4/2004 13:54
5/11/2004 16:37
5/24/2004 10:09
5/15/2004 12:05
5/3/2004 12:37
5/24/2004 11:45
5/11/2004 11:40
5/18/2004 14:05
5/15/2004 11:45
5/24/2004 13:48
5/14/2004 17:03
5/11/2004 16:26
5/24/2004 14:51

I would like a macro to edit each cell and delete the date and keep
the time. I would like to do the opposite, and have another column
with date and time and delete the time and keep the date. I have no
problem with making new columns and copy the same data twice, but
need to have one column with pure date and one with pure time.

I have never done a macro in Excel and as you can see this is all
very new to me.

Thanks,


Hi Bill,

Is there any particular reason you need to use a macro? I only ask
since this is such a trivial task using formulae.

B1 = Int(A1) will give you the date only
B1 = A1 - Int(A1) will give you the time only.

You would have to format the cells as per your preferred date and time
formats of course.

If the cells are text entries, you would have to convert to date /
time values first too.

Alan.

Help with Macro to Convert Date/Time to Time Only
 

If you don't need a macro:

Select your cells and choose Data/Text to Columns. Click the Fixed Width
button, then Next. Click Finish.



In article ,
(Bill) wrote:

This is what I have, I have a group of cell that look like this:
5/24/2004 14:20
5/12/2004 10:22
5/4/2004 13:54
5/11/2004 16:37
5/24/2004 10:09
5/15/2004 12:05
5/3/2004 12:37
5/24/2004 11:45
5/11/2004 11:40
5/18/2004 14:05
5/15/2004 11:45
5/24/2004 13:48
5/14/2004 17:03
5/11/2004 16:26
5/24/2004 14:51

I would like a macro to edit each cell and delete the date and keep
the time. I would like to do the opposite, and have another column
with date and time and delete the time and keep the date. I have no
problem with making new columns and copy the same data twice, but need
to have one column with pure date and one with pure time.

I have never done a macro in Excel and as you can see this is all very
new to me.

Thanks,

Help with Macro to Convert Date/Time to Time Only
 

"JE McGimpsey" wrote in message
...

If you don't need a macro:

Select your cells and choose Data/Text to Columns. Click the Fixed
Width button, then Next. Click Finish.


Hi,

I think he might have to use a space as a delimiter to overcome the
difference in string length between 23/9/2004 and 23/10/2004.

However, better solution than my formulae!

Alan.

Help with Macro to Convert Date/Time to Time Only
 

In my XL version, at least (XL04), the displayed date format doesn't
matter - in the Text to Columns dialog, the data is displayed in
mm/dd/yyyy hh:mm format, even if the cell format is m/d/yy hh:mm


In article ,
"Alan" wrote:

I think he might have to use a space as a delimiter to overcome the
difference in string length between 23/9/2004 and 23/10/2004.

Help with Macro to Convert Date/Time to Time Only
 

hi,
this should do it.
Range("B1:B15").Select
Selection.Insert Shift:=xlDown
Range("A1:A15").Select
Selection.TextToColumns Destination:=Range("A1"),
DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(9, 1))
Columns("B:B").EntireColumn.AutoFit
Range("A1:A15").Select
Selection.NumberFormat = "m/d/yy"

-----Original Message-----
This is what I have, I have a group of cell that look
like this:
5/24/2004 14:20
5/12/2004 10:22
5/4/2004 13:54
5/11/2004 16:37
5/24/2004 10:09
5/15/2004 12:05
5/3/2004 12:37
5/24/2004 11:45
5/11/2004 11:40
5/18/2004 14:05
5/15/2004 11:45
5/24/2004 13:48
5/14/2004 17:03
5/11/2004 16:26
5/24/2004 14:51

I would like a macro to edit each cell and delete the
date and keep
the time. I would like to do the opposite, and have
another column
with date and time and delete the time and keep the
date. I have no
problem with making new columns and copy the same data
twice, but need
to have one column with pure date and one with pure time.

I have never done a macro in Excel and as you can see
this is all very
new to me.

Thanks,
.