Forum Discussion

saud968's avatar
saud968
Memorable Member
2 years ago
Solved

Text to duration format

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 

 

Data Protection
Need Help
Tips and Tricks
  • mh2587's avatar
    mh2587
    2 years ago
    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's avatar
    mh2587
    2 years ago
    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")

5 Replies

  • mh2587's avatar
    mh2587
    Super User
    2 years ago

     

    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")

     

      • mh2587's avatar
        mh2587
        Super User
        2 years ago
        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")