Forum Discussion

saud968's avatar
saud968
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...
  • lbendlin's avatar
    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")