Convert hhhh:mm:ss to hh:mm:ss (macro ?)


G

Guest

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
 
Ad

Advertisements

R

Ron Rosenfeld

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top