I am trying to find out the total business hours a case has been open using the createddate field on the case. I found the formula below on SuiteAnswers. The formula is giving me accurate results but the problem is that it's a text formula giving me a string with the number of days, hours, and minutes. I need the result represented only as total hours. Becuase this is a numeric formula I can't just multiply the results by 24.
I'm trying to use this formula in the results tab and the highlighting tab of a saved search. Any help would be very much appreciated.
CASE WHEN {closed} IS NULL THEN decode(((TRUNC({now},'D')-TRUNC({createddate}+6,'D'))/7*5)+MOD(7-TO_CHAR({createddate},'D'),6)+LEAST(TO_CHAR({now},'D')-2,5)- 1,-1,0,((TRUNC({now},'D')-TRUNC({createddate}+6,'D'))/7*5)+MOD(7-TO_CHAR({createddate},'D'),6)+LEAST(TO_CHAR({now},'D')-2,5)-1)+ decode(trunc({createddate}),trunc({now}),0,trunc(((decode(to_char({createddate},'D'),1,0,7,0,case when to_char({createddate},'hh24') between 8 and 17 then 17-to_char({createddate},'hh24') when to_char({createddate},'hh24')<8 then 10 else 0 end)+decode(to_char({now},'D'),1,0,7,0,case when to_char({now},'hh24') between 8 and 18 then to_char({now},'hh24')-8 when to_char({now},'hh24')<8 then 0 else 10 end))+trunc((decode(to_char({createddate},'D'),1,0,7,0,case when to_char({createddate},'hh24') between 8 and 17 then 60-to_char({createddate},'mi') else 0 end)+decode(to_char({now},'D'),1,0,7,0,case when to_char({now},'hh24') between 8 and 17 then to_number(to_char({now},'mi')) else 0 end))/60))/10))||' days '||mod(decode(to_char({createddate},'D'),1,0,7,0,case when to_char({createddate},'hh24') between 8 and 17 then 17-to_char({createddate},'hh24') when to_char({createddate},'hh24')<8 then 10 else 0 end)+decode(to_char({now},'D'),1,0,7,0,case when to_char({now},'hh24') between 8 and 18 then to_char({now},'hh24')-8 when to_char({now},'hh24')<8 then 0 else 10 end)+trunc((decode(to_char({createddate},'D'),1,0,7,0,case when to_char({createddate},'hh24') between 8 and 17 then 60-to_char({createddate},'mi') else 0 end)+decode(to_char({now},'D'),1,0,7,0,case when to_char({now},'hh24') between 8 and 17 then to_number(to_char({now},'mi')) else 0 end))/60),10)||' hours '||mod((decode(to_char({createddate},'D'),1,0,7,0,case when to_char({createddate},'hh24') between 8 and 17 then 60-to_char({createddate},'mi') else 0 end)+decode(to_char({now},'D'),1,0,7,0,case when to_char({now},'hh24') between 8 and 17 then to_number(to_char({now},'mi')) else 0 end)),60)||' mins ' ELSE decode(((TRUNC({closed},'D')-TRUNC({createddate}+6,'D'))/7*5)+MOD(7-TO_CHAR({createddate},'D'),6)+LEAST(TO_CHAR({closed},'D')-2,5)- 1,-1,0,((TRUNC({closed},'D')-TRUNC({createddate}+6,'D'))/7*5)+MOD(7-TO_CHAR({createddate},'D'),6)+LEAST(TO_CHAR({closed},'D')-2,5)-1)+ decode(trunc({createddate}),trunc({closed}),0,trunc(((decode(to_char({createddate},'D'),1,0,7,0,case when to_char({createddate},'hh24') between 8 and 17 then 17-to_char({createddate},'hh24') when to_char({createddate},'hh24')<8 then 10 else 0 end)+decode(to_char({closed},'D'),1,0,7,0,case when to_char({closed},'hh24') between 8 and 18 then to_char({closed},'hh24')-8 when to_char({closed},'hh24')<8 then 0 else 10 end))+trunc((decode(to_char({createddate},'D'),1,0,7,0,case when to_char({createddate},'hh24') between 8 and 17 then 60-to_char({createddate},'mi') else 0 end)+decode(to_char({closed},'D'),1,0,7,0,case when to_char({closed},'hh24') between 8 and 17 then to_number(to_char({closed},'mi')) else 0 end))/60))/10))||' days '||mod(decode(to_char({createddate},'D'),1,0,7,0,case when to_char({createddate},'hh24') between 8 and 17 then 17-to_char({createddate},'hh24') when to_char({createddate},'hh24')<8 then 10 else 0 end)+decode(to_char({closed},'D'),1,0,7,0,case when to_char({closed},'hh24') between 8 and 18 then to_char({closed},'hh24')-8 when to_char({closed},'hh24')<8 then 0 else 10 end)+trunc((decode(to_char({createddate},'D'),1,0,7,0,case when to_char({createddate},'hh24') between 8 and 17 then 60-to_char({createddate},'mi') else 0 end)+decode(to_char({closed},'D'),1,0,7,0,case when to_char({closed},'hh24') between 8 and 17 then to_number(to_char({closed},'mi')) else 0 end))/60),10)||' hours '||mod((decode(to_char({createddate},'D'),1,0,7,0,case when to_char({createddate},'hh24') between 8 and 17 then 60-to_char({createddate},'mi') else 0 end)+decode(to_char({closed},'D'),1,0,7,0,case when to_char({closed},'hh24') between 8 and 17 then to_number(to_char({closed},'mi')) else 0 end)),60)||' mins ' END