Forum Discussion

saud968's avatar
saud968
Memorable Member
2 years ago
Solved

Time format from text

I have to subtract total hours minus the formatted AHT now. I have converted the formatted AHT column in TIME hh:mm: ss format but the Total hour column is getting an error. Further, I converted the total hours to another from 169h 11m 52s and used a similar dax formattedAHT named as TotalTH


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

The difference is more than 24 hours DATE_ENTERED_SR_EST - 09/01/2023 00:19:09 and DATE_CLOSED_UTC_SR - 09/08/2023 01:31:00 

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"

FormattedTH =
VAR AHTText = support__cw_ops_manage_case_merge_temp[Total Hours3]
VAR HoursText = IFERROR(VALUE(LEFT(AHTText, FIND("h", AHTText & "h") - 2)), 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)

VAR TotalTimeValue =
    (HoursText * 3600 + MinutesText * 60 + SecondsText) / 86400

RETURN
    FORMAT(TotalTimeValue, "hh:mm:ss")
The output i am getting is 16:11:52, however, it should be 169:11:52



Ritaf1983 amitchandak parry2k vicky_ Fowmy 

General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • saud968's avatar
    saud968
    2 years ago

    This is now resolved by below 2 dax's


    THF = 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,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")  

2 Replies

  • ryan_mayu's avatar
    ryan_mayu
    Super User
    2 years ago

     

    saud968 

     

    time function

     

    hour 

    A number from 0 to 23 representing the hour.

    Any value greater than 23 will be divided by 24 and the remainder will be treated as the hour value.

    TIME Function (DAX) | Microsoft Learn

     

    format function

    h :Display the hour as a number without a leading zero (0-23).
    hh: Display the hour as a number with a leading zero (00-23).

     

    FORMAT function (DAX) - DAX | Microsoft Learn

     

    maybe you can change it back to TEXT type

    return INT(TotalTimeValue/3600) & ":"&INT(MOD(TotalTimeValue,3600)/60)&":"&INT(MOD(TotalTimeValue,60))

     

    • saud968's avatar
      saud968
      Memorable Member
      2 years ago

      This is now resolved by below 2 dax's


      THF = 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,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")