Search code examples
excelexcel-formulaexcel-2010excel-2007excel-2013

Convert String To Time Duration In Excel In Any Of The Different Formats

Previous asked question

I have asked a similar question on here, and the answer is accurate and efficient, but it only work when the input is

` 1 h 1 m 1 s

format. If other formats, it will cause error.

The solution from the question

= VALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A5," h ",":")," m ",":")," s",""))

-------------------------------------------------------------------------------------------------------

My question as below:

May I know the way to convert the following inputs to the expected output?

  1. 1 h 48 m 5 s to 1:48:05
  2. 48 m 5 s to 0:48:05
  3. 5 s to 0:0:05
  4. 1 h 5 s to 1:0:5
  5. Other combination of h m s to 0:0:0 format

Thanks in advance

Based on the two formulaes

= SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(IF(ISNUMBER(SEARCH("m",S32)),IF(ISNUMBER(SEARCH("h",S32)),"","0h"),"0h0m")&S32," ",""),"m",":"),"h",":"),"s","")*1

and

=(IF(ISERROR(SEARCH("h",A1)),"0:","")&IF(ISERROR(SEARCH("m",A1)),"0:","")&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"h",":"),"m",":"),"s",""))+0

If the input is it

1 h 5 s

it causes error.

The result should follow the test cases as below

Test Case

Solution

  • The simple way would be to add missing parts like:

    =(IF(ISERROR(SEARCH("h",A1)),"0:","")&IF(ISERROR(SEARCH("m",A1)),"0:","")&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"h",":"),"m",":"),"s",""))+0

    and then directly format it so it looks like you want it...