Manipulating time data (format 1 h 27m)

**AHFoddeR** · 10-25-2012, 11:52 PM

Hey all!

Great to be a new member on the forum.

I've got a series of time data (durations) in the following formats:

7m
35m
1 h 6m
1 h 25m

I want it to look like this:

0:07
0:35
1:06
1:25

I figured out a formula using nested if statements but I know there's a simpler way.

My solution counted the number of character, then manipulated the string in different ways depending on the number of characters

E.g. =IF(LEN(A1)=2,"0:0"&LEFT(A1,1),IF(LEN(A1)=3.... etc

It worked but it wasn't pretty.

Who can enlighten me?

Thanks guys!

FoddeR

**grizzly6969** · 10-26-2012, 12:51 AM

not sure if this will work for you but
Format Cell / Custom Then 0\:00

**Armando Montes** · 10-26-2012, 01:14 AM

You might try Find and Replace

**TonyB51** · 10-26-2012, 01:26 AM

Hi,

This formula works, is it less or more than what you had.

=IF(ISNUMBER(FIND("h",A1)),LEFT(A1,(FIND(" h ",A1)-1))&":"&IF(LEN(A1)-(FIND("h",A1)+2)=1,"0"&MID(A1,FIND("h",A1)+2,1),MID(A1,FIND("h",A1)+2,2)),"0:"&IF(LEN(A1)=2,"0"&LEFT(A1,1),LEFT(A1,2)))

Cheers

TonyB

**Armando Montes** · 10-26-2012, 04:39 AM

Try this:
=SUM(MID(0&A1&"0000",FIND({"s","m","h","d"},0&A1&"xxsmhd")-2,2)/{86400,1440,24,1})
Format cell as h:mm
Houdini formula at:
http://stackoverflow.com/questions/9...es-into-hhmmss

**AHFoddeR** · 10-26-2012, 07:06 PM

@TonyB: This formula worked but it was more or less what I had already. Roughly the same logic. Thanks though ;-)

@Armando: Awesome. This is what I was looking for. Short and sweet. I can't seem to figure out how exactly it works... Any explanations?

Thanks for the help!

FoddeR