Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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
up vote 6 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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .