Converting times (:xx to x:xx:xx)

[Scootman1911]

I need some help with converting times from :xx format to x:xx:xx without having to manually edit every single cell. An employee spent hours of work on creating these data points for me but didn't enter the times correctly. Essentially, he entering a time stamp of how long into a video that certain things happened so if something happened 3 seconds in, he entered this as :03 when I needed it entered as 0:00:03 to work with this other formula I have.

I'm not super good with formulas and macros so I'm sure there's an easy way to do this but I must not be using the correct search terms to be able to find anything. If anyone can help me out, that'd be amazing!

[PCI]

What is the max and the min of the values, is it always an integer?
If i is a format topic, you try using a custom format like:
00 or 00 ":" 00 ":" 00

[Scootman1911]

It will always be an integer. Min value would be 0:00:00 (beginning of video) and max would be somewhere around 5:25:00 (end of video) depending on where the camera decided to chapter the video. I have a formula that will take the chapter time (which is what I had my employee enter) and convert that to real time but it only works if I have the video time in x:xx:xx format. When it's just :xx or x:xx, I get a #VALUE! error.

I've attached an image with two lines of data: the first being the error line, the second showing the correct output. I just manually edited the second line and I could do this for every cell but I have 600-700 cells that I'd have to edit in this way. Looking through the data I have, it looks like most of it is entered in a :xx:xx format so I'll mostly be needing to add just a leading 0 in the Hour section of time. If that's easier and I can get help with that, I can manually edit the ones that need a 0 in the hours and minutes section.
Example.png

[PCI]

Is it possible you send a short Excel file with the data you shown in the display.
What is confusing: The value of a cell is what is used by formula, the format of a cell don't have any influence of the value and therefore on the result of the formula.
It is difficult to see what make the error with value 1 and gives a correct result with value 3.

[Scootman1911]

It's showing an error because the formula in far right cell is using the TIME formula and it sees that there is no hours and minutes to add/subtract from so it gives an error. At least that's my theory but I'm not the best with Excel.

Here is the formula that's in the far right cell:

Please Login or Register  to view this content.

[Brawnystaff]

Looking over your original question regarding converting :03 to 0:00:03, you can use the following macro to do the conversion:

Please Login or Register  to view this content.

Note that is will create the new value one cell to the right of the selected values (offset 0,1).

[Scootman1911]

Thanks a ton! You saved me hours and hours of redoing this guy's work. I haven't tried it yet as I'm wrapping up some other stuff at the moment but if I have a time of say, 1:13, will that macro make it 0:00:01:13?

[Brawnystaff]

Actually, the macro above would only work for time formated as :03. To format 1:13 as 0:01:13, you would use the following:

Please Login or Register  to view this content.

The main difference between this macro an the last is the insertion of the zeros in the beginning of the formula (00: vs. 00:00). Probably could put an "if" statement in the macro to figure out the syntax, but at this point, you could use these macro separately to make the changes.

[Scootman1911]

You're my hero right now lol! I figured it would be a simple change in the macro but wasn't sure where that change would go. I'm currently taking an online course about Excel but it hasn't gotten into the macros yet so thank you so much for your help!