Text to Time format

[saud968]

Hi, 

I have a calculated column in text type - 

Total Hours =

    VAR StartDateTime = support__cw_ops_manage_case_merge_temp[DATE_ENTERED_SR_EST]

    VAR EndDateTime = support__cw_ops_manage_case_merge_temp[DATE_CLOSED_UTC_SR]

    VAR DurationInHours = (EndDateTime - StartDateTime) * 24

    VAR Hours = TRUNC(DurationInHours)

    VAR Minutes = TRUNC((DurationInHours - Hours) * 60)

    VAR Seconds = ROUNDUP((DurationInHours - Hours - Minutes / 60) * 3600, 0)

    RETURN

        FORMAT(Hours, "00") & ":" & FORMAT(Minutes, "00") & ":" & FORMAT(Seconds, "00")

Now the reason for the conversion time is that I have another calculated column 

FormattedAHT =

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

I have to now subtract total hours minus the formatted AHT. I have converted the formatted AHT column in TIME hh:mm: ss format but the Total hour's column is getting an error 

For example in the first dates in the image, the total hours is 169:11:52 it should be minus 1:01:55 which should give output around 168:09:57 approx. but due to a format issue, I am unable to get the result.  Please review the file for more information - https://drive.google.com/file/d/1ua-mfEICRQDFBd7xkPMl_3H1lblT-c7Q/view?usp=drivesdk

@lbendlin @Ahmedx 

@Idrissshatila @amitchandak @parry2k @technolog 

[saud968 · Accepted solution]

Please find the file https://drive.google.com/file/d/1ua-mfEICRQDFBd7xkPMl_3H1lblT-c7Q/view?usp=drivesdk

thank you for your help.

[Ahmedx]

I'm not sure that I fully understood what you need, but I created several calculated columns and one measure, I really hope this will help you
the logic is like this:
you need to convert everything into seconds, then calculate it and then convert it back to "hh:mm:ss"

[saud968]

@Ahmedx how do i fix the issue with my version of Power BI, please guide

[Ahmedx]

you need to open it on another computer where the latest version of power bi should be

final = 
   VAR _Duration = SUM('support__cw_ops_manage_case_merge_temp'[Total Hoursxx])- SUM(support__cw_ops_manage_case_merge_temp[timtext_To_Time 2])
VAR _Hours = INT(_Duration/3600)
VAR Minutes =
    INT ( MOD( _Duration - ( _Hours * 3600 ),3600 ) / 60)
    VAR Seconds =
    ROUNDUP(MOD ( MOD( _Duration - ( _Hours * 3600 ),3600 ), 60 ),0)
// These intermediate variables ensure that we have leading zero's concatenated onto single digits
// Hours with leading zeros
VAR H =
    IF (ISBLANK(_Hours), "00",
      IF(   LEN (  _Hours ) = 1,
        CONCATENATE ( "0", _Hours ),
        CONCATENATE ( "", _Hours )
      ))
// Minutes with leading zeros
VAR M =
    IF ( ISBLANK(Minutes), "00",
        IF(LEN ( Minutes ) = 1,
        CONCATENATE ( "0", Minutes ),
        CONCATENATE ( "", Minutes )
    ))
// Seconds with leading zeros
VAR S =
    IF ( ISBLANK(Seconds), "00",
       IF( LEN ( Seconds ) = 1,
        CONCATENATE ( "0", Seconds ),
        CONCATENATE ( "", Seconds )
    ))
// Now return hours, minutes and seconds with leading zeros in the proper format "hh:mm:ss"
RETURN

    CONCATENATE (
        H,
        CONCATENATE ( ":", CONCATENATE ( M, CONCATENATE ( ":", S ) ) )
    )

[saud968]

Thi s is the latest version - Version: 2.123.742.0 64-bit (November 2023)

[Ahmedx]

[saud968]

I have the same version and your sample file it is working perfectly but not working in my OG file

[Ahmedx]

I don't know how to help you

[saud968]

Hi @Ahmedx I used your previously helped dax. 

1. I created the below format 

Total Hours3 =

    VAR StartDateTime = support__cw_ops_manage_case_merge_temp[DATE_ENTERED_SR_EST]

    VAR EndDateTime = support__cw_ops_manage_case_merge_temp[DATE_CLOSED_UTC_SR]

    VAR DurationInHours = (EndDateTime - StartDateTime) * 24

    VAR Hours = TRUNC(DurationInHours)

    VAR Minutes = TRUNC((DurationInHours - Hours) * 60)

    VAR Seconds = ROUNDUP((DurationInHours - Hours - Minutes / 60) * 3600, 0)

    RETURN

        FORMAT(Hours, "0") & "h " & FORMAT(Minutes, "0") & "m " & FORMAT(Seconds, "0") & "s"


2. then tried your Dax you helped for FormattedAHT

FormattedAHT =

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

I had to change it a little as below

FormattedAHT =

VAR AHTText = support__cw_ops_manage_case_merge_temp[AHT Text]

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

VAR MinutesStartPos = IFERROR(FIND("h", AHTText) + 3, 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) + 3, 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")

With the changes above i get output for 169h 11m 52s as 16:01:02 please help me figure out to get the complete number

[saud968]

No worries, you have helped a lot. I will figure something out and will let you know how I was able to get it done. Give me some time.

Thank you so much for your help 😍

[saud968]

@Ahmedx As you can see the Final (Dax) that you helped with does not give the correct output it also it is in text format. Request you to please check the file one more time if possible, Please 🙂

[saud968]

This is great, to let you know what i want is the difference hours between FormattedAHT and Total Hours in hh:mm:ss format. The file you shared the dax does not work on my version of power BI - Version: 2.123.742.0 64-bit (November 2023)

[saud968]

@Ahmedx Please review the https://drive.google.com/file/d/1ua-mfEICRQDFBd7xkPMl_3H1lblT-c7Q/view?usp=drivesdk and help me out with yet another issue

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[saud968]

@lbendlin Please find the file https://drive.google.com/file/d/1ua-mfEICRQDFBd7xkPMl_3H1lblT-c7Q/view?usp=drivesdk please help out

[lbendlin · Accepted solution]

Total Hours = 
    VAR h= ([DATE_CLOSED_UTC_SR]-[DATE_ENTERED_SR_EST])*24
    var hr= ROUNDDOWN(h,0)
    var m = (h-hr)*60
    var mn = ROUNDDOWN(m,0)
    var s= rounddown((m-mn)*60,0)
    return format(hr,"00:") & format(mn,"00:") & format (s,"00")

Diff = 
var t = SUBSTITUTE(SUBSTITUTE(SUBSTITUTE([AHT Text],"h","|"),"m","|"),"s","")
var d = if(PATHLENGTH(t)=3,value(pathitem(t,1)+DIVIDE(value(pathitem(t,2)),60)+DIVIDE(value(pathitem(t,3)),60*60),DIVIDE(value(pathitem(t,1)),60)+DIVIDE(value(pathitem(t,2)),60*60))
VAR h= ([DATE_CLOSED_UTC_SR]-[DATE_ENTERED_SR_EST])*24-d
var hr= ROUNDDOWN(h,0)
var m = (h-hr)*60
var mn = ROUNDDOWN(m,0)
var s= rounddown((m-mn)*60,0)
return format(hr,"00:") & format(mn,"00:") & format (s,"00")

[saud968]

Diff = var t = SUBSTITUTE(SUBSTITUTE(SUBSTITUTE([AHT Text],"h","|"),"m","|"),"s","")

var d = if(PATHLENGTH(t)=3,DIVIDE(value(pathitem(t,1)),24)+DIVIDE(value(pathitem(t,2)),24*60)+DIVIDE(value(pathitem(t,3)),24*60*60),DIVIDE(value(pathitem(t,1)),24*60)+DIVIDE(value(pathitem(t,2)),24*60*60))

VAR h= ([DATE_CLOSED_UTC_SR]-[DATE_ENTERED_SR_EST]-d)*24

var hr= ROUNDDOWN(h,0)

var m = (h-hr)*60

var mn = ROUNDDOWN(m,0)

var s= rounddown((m-mn)*60,0)

return format(hr,"00:") & format(mn,"00:") & format (s,"00")  

I am unable to convert this is time (Data Type) format the reason for this is I need to get an average for the time. getting the below please assist

[lbendlin]

That is a data quality issue.  Your End date is before the Start date by two seconds.

[saud968 · Accepted solution]

Please find the file https://drive.google.com/file/d/1ua-mfEICRQDFBd7xkPMl_3H1lblT-c7Q/view?usp=drivesdk

thank you for your help.