Two part number problem. Been at it for hours. Any help would be really appreciated.

[shenstevepre]

Hello. This is my first time on this forum so I hope I'm following the proper protocol here. If anyone can help me with this I would really appreciate it. I have a column of about one thousand times. These times were duration periods recorded and entered in a mm:ss, thats minutes:seconds, format. I need to find a way to convert this format into hh:mm:ss. So far I haven't been able to do this. I'm running into a bunch of snags.

I thought I might be able to create three new columns - an hh, mm, and ss column and then combine the three columns in a fourth folumn using the =time(h,m,s) function. However in order to do this I need to first split up the mm:ss durations into a mm and ss column. I tried using =left and =right functions but that isn't working and I have no idea why.

I have some duration periods that are made up just seconds eg 0:45 (being 45 seconds) and some that are made up of single minute plus durations by which I mean durations less than ten minutes long, eg 1:45 (minute and 45 seconds) and some made up of double minute durations, by which I mean durations more than ten digits long eg 11:45.

I figure that I will have to convert all the times to double digit minute formats (eg turning 07 min 15 sec from 7:15 to 07:15), but I don't know for certain.

1) How can I alter duration column to make it uniform and get it so that all durations have two digits before colon?

2) Second how can I turn mm:ss into hh:mm:ss short of typing it over one thousand times?

Once again, any help would be greatly appreciated.

[protonLeah]

To Attach a Workbook:
1. Click on Go Advanced
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit

[FDibbins]

if the data in the cells is text, that looks like time/value, try using =timevalue() to convert it?