Forum Discussion

Memorable Member

2 years ago

Solved

Text to Time format

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_c...

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

thank you for your help.

lbendlin

2 years ago

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

AHT Analysis D1.pbix3.7 MB

saud968

Memorable Member

2 years ago

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

lbendlin

Super User

2 years ago

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

AHT Analysis D1.pbix3.7 MB

saud968
Memorable Member
2 years ago
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
  Super User
  2 years ago
  That is a data quality issue. Your End date is before the Start date by two seconds.