# Manipulating time data (format 1 h 27m)

1. ## Manipulating time data (format 1 h 27m)

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

2. ## Re: Manipulating time data (format 1 h 27m)

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

3. ## Re: Manipulating time data (format 1 h 27m)

You might try Find and Replace

4. ## Re: Manipulating time data (format 1 h 27m)

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

5. ## Re: Manipulating time data (format 1 h 27m)

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

6. ## Re: Manipulating time data (format 1 h 27m)

@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

There are currently 1 users browsing this thread. (0 members and 1 guests)