How do I convert a :mm:ss entry to h:mm:ss format in excel?

Question

I'm using CenterVue and trying to export call center reports to a spreadsheet. Unfortunately, sometimes the CenterVue data if it was less than an hour ends up formatted with a colon before the minutes and seconds. It's impossible to add a cell that has a colon before the minutes. Is there a way to easily get the format to change to h:mm:ss instead of manually making the change? For example changing :58:30 to just 58:30 or 0:58:30.

Answer 1

If your cell is A1, then you may use this function
=IF( left( A1, 1)= ":", mid( A1, 2, 500), A1)

This formula will remove the colon if found in the first of that cell

If this is not what you need, mail me through Yahoo! Answers

Read my profile, I am the XLMan

Answer 2

This is how you can change :58:30 to 58:30 to hundreds of cells at once:

First, select all cells in the Centervue Data column that contain the :xx:xx format, or simply select the entire column by clicking at the letter of the row.

Second, after you highlight all of the cells in the column, go to the Data menu in Excel, then choose "Text to Columns", then select "Delimited Width", then click "Next".

Third, where you see the data you selected, use the mouse to click and drag. You'll notice a line appears. Drag the line where it's seperating the first : from the rest of the data.

Then, click Next. The first : will be highlighted. On the right click "Do not import Data"

Last, click on the remaining data to the right of the first :. It will highlight when you click on it. Click Finish. This will automatically remove all of the first :'s from the cells. You can do this to as many as you select at one time.

I work at a call center too by the way in the IT field. If you have questions, or need a little more assistance with these directions, email me [e-mail]

Edit: I tested this with a row of a few hundred cells with the :33:33 format, and it removed the first : of every single cell. If you need screenshots, email me.

Answer 3

There are a lot of ways you can have Excel display dates and times. Here's a tutorial that should help you out:
http://www.worldstart.com/tips/tips.php/1058

Answer 4

HUH?