Forum Discussion

saud968's avatar
saud968
Icon for Memorable Member rankMemorable 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 ...
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")  

ryan_mayu's avatar
ryan_mayu
Icon for Super User rankSuper 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
Icon for Memorable Member rankMemorable 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")