Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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, |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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, . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I need to convert a date and a time from PST to EST, how can I do | New Users to Excel | |||
Convert date/time to time only in Excel? | Excel Discussion (Misc queries) | |||
convert second to date and time | Excel Worksheet Functions | |||
Convert date and time | Excel Discussion (Misc queries) | |||
Calculating days & time left from start date/time to end date/time | Excel Worksheet Functions |