Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Convert hhhh:mm:ss to hh:mm:ss (macro ?)
British Telecom use hhhh:mm:ss as a time format for length of call in downloaded accounts. Excel97 does not recognise this as a number so I cannot reformat to hh:mm:ss. I've tried (with no experience) a macro to remove the 2 leading zeros, but if I set it up by converting the first 10 times, then try to run it starting at the 11th time, it does not convert the second set of ten, but simply replicates the times created (for the first ten) when setting up the macro. BT Bill |
#2
|
|||
|
|||
On Mon, 7 Mar 2005 23:53:01 -0800, "BT Bill"
wrote: British Telecom use hhhh:mm:ss as a time format for length of call in downloaded accounts. Excel97 does not recognise this as a number so I cannot reformat to hh:mm:ss. I've tried (with no experience) a macro to remove the 2 leading zeros, but if I set it up by converting the first 10 times, then try to run it starting at the 11th time, it does not convert the second set of ten, but simply replicates the times created (for the first ten) when setting up the macro. BT Bill No need for a macro. You can use the formula: =--MID(A1,3,8) to do the conversion. You may also use the Data/Text to Columns wizard to do a conversion in place if the range is in a column. 1. Select the range. 2. Date/Text to Columns 3. Fixed Width Next 4. Drop a break line just after the 00 Next 5. The first column (00) will be selected. Check the "Do not import column" box. 6. Finish The above could be recorded as a macro. Finally, to do this as a macro, after selecting the range of values to convert: ======================== Sub foo() Dim c As Range For Each c In Selection c.Value = Mid(c.Text, 3, 8) Next c End Sub ========================== --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Playing a macro from another workbook | Excel Discussion (Misc queries) | |||
automatic macro update | Excel Worksheet Functions | |||
Date macro | Excel Discussion (Misc queries) | |||
Macro and If Statement | Excel Discussion (Misc queries) | |||
Macro Formula revision? | Excel Worksheet Functions |