Help with Macro to Convert Date/Time to Time Only

  • Thread starter Thread starter Bill
  • Start date Start date
B

Bill

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,
 
Bill said:
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,
Click to expand...

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.
 
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.
 
JE McGimpsey said:
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.
Click to expand...

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.
 
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
 
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"
 
Back
Top