Excel 2007 : Converting "mm min ss s" time format to "hh:mm:ss"

**blackmilk** · 04-19-2012, 12:15 PM

Hi,
Hope you guys can help.

I have the following data:

18 min 56 s

15 min 52 s

14 min 50 s

Is there a way to convert it to:

00:18:56

00:15:52

00:14:50

?

Thanks!
David

**JieJenn** · 04-19-2012, 12:18 PM

Assuming your value is stored in cell I9, try

="00:"&LEFT(I9,2)&":"&MID(I9,8,2)

**blackmilk** · 04-19-2012, 12:33 PM

Thanks so much, that worked!

Unfortunately, I just realized I have some cells with time formats that don't fit to this formula.

For example:

8 s

13 s

1 min 22 s

Converts to:

00:8 :

00:13:

00:1 :2

Is there a way to fit these to the "hh:mm:ss" format too?

Thanks so much again!
David

**JieJenn** · 04-19-2012, 12:35 PM

How about hours?

**blackmilk** · 04-19-2012, 12:42 PM

Do you mean if I have cells that contain more than 59 minutes?
If so, no.

Here are some examples of the data I have:
18 min 56 s
19 min 3 s
14 s
1 min 22 s
1 min 0 s

Thanks,
David

**blackmilk** · 04-22-2012, 05:36 PM

Please help?

**Cutter** · 04-22-2012, 06:26 PM

Maybe
="00:"&IF(COUNTIF(B4,"*m*"),TEXT(TRIM(LEFT(B4,2)),"00"),"00")&":"&TEXT(TRIM(SUBSTITUTE(RIGHT(B4,4)," s","")),"00")

EDIT: slightly shorter version
="00:"&IF(LEN(B4)>4,TEXT(TRIM(LEFT(B4,2)),"00"),"00")&":"&TEXT(TRIM(SUBSTITUTE(RIGHT(B4,4)," s","")),"00")

EDIT: even shorter version
=TIME(0,IF(LEN(B4)>4,TRIM(LEFT(B4,2)),0),TRIM(LEFT(RIGHT(B4,4),2)))
format cell as hh:mm:ss

**shg** · 04-22-2012, 06:31 PM

-- Deleted --

**Cutter** · 04-22-2012, 07:07 PM

@shg
That's not working - check the 3rd entry in the examples you give - "14 s" yields 0:14:00 but should be 00:00:14

**shg** · 04-22-2012, 07:23 PM

Thanks, Cutter -- when I fixed it it was more complicated than yours.

**Cutter** · 04-22-2012, 07:27 PM

Hey - does that mean I get a 'star tap' from a master????????

**shg** · 04-22-2012, 10:37 PM

Indeed, and done.

**Cutter** · 04-23-2012, 06:47 AM

Hey, thanks for that. I was kidding, but no take-backs! I'm off to the framing shop.

But I came up with a shorter version:
=TIME(0,IF(LEN(B4)>4,TRIM(LEFT(B4,2)),0),TRIM(LEFT(RIGHT(B4,4),2)))
format cell as hh:mm:ss

EDIT: and even shorter
=TIME(0,TRIM(LEFT(B4,2))*(LEN(B4)>4),TRIM(LEFT(RIGHT(B4,4),2)))

**blackmilk** · 04-23-2012, 04:08 PM

Thanks so much Cutter, that worked beautifully!

**Cutter** · 04-23-2012, 04:12 PM

Which one????

You're welcome.

**blackmilk** · 04-23-2012, 04:14 PM

I only tried the very short one

Excel 2007 : Converting "mm min ss s" time format to "hh:mm:ss"

LinkBack

Thread Tools

Rate This Thread

Display

Converting "mm min ss s" time format to "hh:mm:ss"

Re: Converting "mm min ss s" time format to "hh:mm:ss"

Re: Converting "mm min ss s" time format to "hh:mm:ss"

Re: Converting "mm min ss s" time format to "hh:mm:ss"

Re: Converting "mm min ss s" time format to "hh:mm:ss"

Re: Converting "mm min ss s" time format to "hh:mm:ss"

Re: Converting "mm min ss s" time format to "hh:mm:ss"

Re: Converting "mm min ss s" time format to "hh:mm:ss"

Re: Converting "mm min ss s" time format to "hh:mm:ss"

Re: Converting "mm min ss s" time format to "hh:mm:ss"

Re: Converting "mm min ss s" time format to "hh:mm:ss"

Re: Converting "mm min ss s" time format to "hh:mm:ss"

Re: Converting "mm min ss s" time format to "hh:mm:ss"

Re: Converting "mm min ss s" time format to "hh:mm:ss"

Re: Converting "mm min ss s" time format to "hh:mm:ss"

Re: Converting "mm min ss s" time format to "hh:mm:ss"

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions