Formula to convert mm:ss into h:mm:ss

[brunette07]

I am working in Excel 2010 and need to convert mm:ss (246:00:00) into h:mm:ss (4:06:00) by using a formula. I know you can convert the data by manually inserting a "0" before the mm:ss, but that could take hours (I have a lot a data).

Additionally, the mm:ss is displayed as 246:00:00, but when I click into the cell (fx) it shows the following: 1/10/1900 6:00:00 AM.

Please help!

Thx.

 

[rguy84]

Try =text(cell,"h:mm:ss")

 

[shg]

The value in the cell is 246 hours.

If you wanted to convert it to 4:06, divide by 60:

=A1/60

 

[brunette07]

Thanks, but I have already tried both those formulas -- the problem is is that even though the cell is showing 246:00:00, when I actually put the curser into the cell it shows: 1/10/1900 6:00:00 AM. Basically, I am not sure how/why the result is 246:00:00 (246 hours).

 

[shg]

246 hours is 10 days and 6 hours, and since Excel counts time starting from 0 Jan 1900, it's also 06:00 on 10 Jan.

 

[brunette07]

I see -- thanks for the help. I have the formula working now. One more thing -- I have a total of 122682:27 -- I try and use the formula =A2/60 and I receive a error of #VALUE!. Can you help?

 

[shg]

You can't manually input values larger than 9999:00

 

[brunette07]

ok -- got it. thanks!