0

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

6

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 :)

  • +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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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