Text to duration format

[saud968]

I am try to convert a text type column to duration format as"h:mm:ss". I am using the DAX as below, however, I am getting correct output for value 1h 2m 3s as 1:02:03, however, for 11m 20s I get 0:00:20

Formatted AHT =

VAR AHTText = support__cw_ops_manage_case_merge_temp[AHT Text]

VAR HoursText = IFERROR(VALUE(LEFT(AHTText, FIND("h", AHTText) - 1)), 0)

VAR MinutesText = IFERROR(

    IF(

        ISNUMBER(FIND("m", AHTText)),

        VALUE(MID(AHTText, FIND("h", AHTText) + 2, FIND("m", AHTText) - FIND("h", AHTText) - 2)),

        VALUE(MID(AHTText, FIND("h", AHTText) + 2, LEN(AHTText) - FIND("h", AHTText) - 1))

    ),

    0

)

VAR SecondsText = IFERROR(

    IF(

        ISNUMBER(FIND("s", AHTText)),

        VALUE(MID(AHTText, FIND("m", AHTText) + 2, FIND("s", AHTText) - FIND("m", AHTText) - 2)),

        VALUE(MID(AHTText, FIND("m", AHTText) + 2, LEN(AHTText) - FIND("m", AHTText) - 1))

    ),

    0

)

RETURN FORMAT(TIME(HoursText, MinutesText, SecondsText), "h:mm:ss")

@lbendlin @Ahmedx 

@Idrissshatila @amitchandak @parry2k @technolog 

[mh2587 · Accepted solution]

FormattedAHT = //Try this one in Calculated column
VAR AHTText = support__cw_ops_manage_case_merge_temp[AHT Text]
VAR HoursText = IFERROR(VALUE(LEFT(AHTText, FIND("h", AHTText & "h") - 1)), 0)
VAR MinutesStartPos = IFERROR(FIND("h", AHTText) + 2, 1)
VAR MinutesEndPos = IFERROR(FIND("m", AHTText & "m", MinutesStartPos), LEN(AHTText))
VAR MinutesText = VALUE(MID(AHTText, MinutesStartPos, MinutesEndPos - MinutesStartPos))
VAR SecondsStartPos = IFERROR(FIND("m", AHTText) + 2, 1)
VAR SecondsEndPos = IFERROR(FIND("s", AHTText & "s", SecondsStartPos), LEN(AHTText))
VAR SecondsText = VALUE(MID(AHTText, SecondsStartPos, SecondsEndPos - SecondsStartPos))
RETURN FORMAT(TIME(HoursText, MinutesText, SecondsText), "h:mm:ss")

[mh2587]

FormattedAHT = //Try this Calculated Column might help you
VAR AHTText = support__cw_ops_manage_case_merge_temp[AHT Text]
VAR HoursText = IFERROR(VALUE(LEFT(AHTText, FIND("h", AHTText) - 1)), 0)
VAR MinutesStartPos = IF(ISNUMBER(FIND("h", AHTText)), FIND("h", AHTText) + 2, 1)
VAR MinutesEndPos = IFERROR(FIND("m", AHTText), LEN(AHTText))
VAR MinutesText = VALUE(MID(AHTText, MinutesStartPos, MinutesEndPos - MinutesStartPos))
VAR SecondsStartPos = IF(ISNUMBER(FIND("m", AHTText)), FIND("m", AHTText) + 2, 1)
VAR SecondsEndPos = IFERROR(FIND("s", AHTText), LEN(AHTText))
VAR SecondsText = VALUE(MID(AHTText, SecondsStartPos, SecondsEndPos - SecondsStartPos))
RETURN FORMAT(TIME(HoursText, MinutesText, SecondsText), "h:mm:ss")

[saud968]

Getting the below error 

[mh2587 · Accepted solution]

FormattedAHT = //Try this one in Calculated column
VAR AHTText = support__cw_ops_manage_case_merge_temp[AHT Text]
VAR HoursText = IFERROR(VALUE(LEFT(AHTText, FIND("h", AHTText & "h") - 1)), 0)
VAR MinutesStartPos = IFERROR(FIND("h", AHTText) + 2, 1)
VAR MinutesEndPos = IFERROR(FIND("m", AHTText & "m", MinutesStartPos), LEN(AHTText))
VAR MinutesText = VALUE(MID(AHTText, MinutesStartPos, MinutesEndPos - MinutesStartPos))
VAR SecondsStartPos = IFERROR(FIND("m", AHTText) + 2, 1)
VAR SecondsEndPos = IFERROR(FIND("s", AHTText & "s", SecondsStartPos), LEN(AHTText))
VAR SecondsText = VALUE(MID(AHTText, SecondsStartPos, SecondsEndPos - SecondsStartPos))
RETURN FORMAT(TIME(HoursText, MinutesText, SecondsText), "h:mm:ss")

[saud968]

Now this error

[mh2587 · Accepted solution]

FormattedAHT = // Analyze yourself please 
VAR AHTText = support__cw_ops_manage_case_merge_temp[AHT Text]
VAR HoursText = IFERROR(VALUE(LEFT(AHTText, FIND("h", AHTText & "h") - 1)), 0)
VAR MinutesStartPos = IFERROR(FIND("h", AHTText) + 2, 1)
VAR MinutesEndPos = IFERROR(FIND("m", AHTText & "m", MinutesStartPos), LEN(AHTText))
VAR MinutesText = IFERROR(VALUE(MID(AHTText, MinutesStartPos, MinutesEndPos - MinutesStartPos)), 0)
VAR SecondsStartPos = IFERROR(FIND("m", AHTText) + 2, 1)
VAR SecondsEndPos = IFERROR(FIND("s", AHTText & "s", SecondsStartPos), LEN(AHTText))
VAR SecondsText = IFERROR(VALUE(MID(AHTText, SecondsStartPos, SecondsEndPos - SecondsStartPos)), 0)
RETURN FORMAT(TIME(HoursText, MinutesText, SecondsText), "h:mm:ss")