Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have a excel column with data like:

58m32s
72m10s
125m02s

And would like to show it like:

00:58:32
01:12:10
02:05:02

What formula combination should i use to make this possible? This should(?) work, but isn't:

=IF(MID(A1,3,1)="m",TIMEVALUE("00:"&LEFT(A1,2)&":"&RIGHT(A1,2)),TIMEVALUE(LEFT(A1,2)&":"&RIGHT(A1,2)&":00"))

Thanks

share|improve this question
add comment

1 Answer

up vote 5 down vote accepted

Well, you have different number of minutes in each case, so a single MID won't work. You might try:

=("00:"&SUBSTITUTE(SUBSTITUTE(A1,"m",":"),"s",""))*1

The innermost SUBSTITUTE substitutes m by : (which is used in time) and the second removes s. You get the following after those two substitutes:

58:32
72:10
125:02

If you just convert those to time now, Excel interpretes there as hh:mm, which is not your intent. Put a "00:" in front to fool Excel:

00:58:32
00:72:10
00:125:02

Now it's in the hh:mm:ss format (somewhat), multiply by 1 to convert this into a value and format it as time: hh:mm:ss. This should do the trick :)

share|improve this answer
    
+1 for cheating Excel. But you have to convert the minutes to hours like the OP wants (thats not a big deal for you, but you forgot it^^) –  nixda Aug 13 '13 at 18:37
    
@nixda Excel does it for you when you convert the value to the format hh:mm:ss =P. And thank you ^^ –  Jerry Aug 13 '13 at 18:38
    
Ah missed the formatting thing. You're right –  nixda Aug 13 '13 at 18:39
    
And it did! Thanks! –  Khrys Aug 13 '13 at 18:43
    
@Khrys You're welcome :) –  Jerry Aug 13 '13 at 19:31
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.