Forum Discussion
saud968
2 years agoMemorable Member
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...
- 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. - 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")