Getting rid of weird time format 01m 16s

**rooonik** · 06-29-2013, 10:19 AM

Hi,

My data contains time lengths in the following format:

2h 18m 43s

01m 16s

I need to use to total number of seconds, so for example for the second one I want to display the number 76.

Any ideas?

**shg** · 06-29-2013, 10:29 AM

Please Login or Register  to view this content.

B1 and copy down:

=86400 * SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(IF(ISERROR(FIND("h", A1)), "0h ", "") & A1, "h ", ":"), "m ", ":"), "s", "")

**rooonik** · 06-29-2013, 10:47 AM

That's awesome!

Thanks a lot!

My bad I forgot to mention that some cells only have seconds, like:

16s

How should I modify your formula? it works great when hours and/

**shg** · 06-29-2013, 10:54 AM

=86400 * SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(IF(ISERROR(FIND("h", A3)), "0h ", "") & IF(ISERROR(FIND("m", A3)), "0m ", "") & A3, "h ", ":"), "m ", ":"), "s", "")