Task

Working hours between 8am -6pm=(NETWORKDAYS(B2,C2)-1)*(“18:00:00”-“8:00:00″)+IF(NETWORKDAYS(C2,C2),MEDIAN(MOD(C2,1),”18:00:00″,”8:00:00″),”18:00:00″)-MEDIAN(NETWORKDAYS(B2,B2)*MOD(B2,1),”18:00:00″,”8:00:00”)

our working hours are between 8am -6pm weekdays, we do not work on weekends. The task can arrive anytime in the system, we pick it up and work on it only between our working hours. The above is the formula I used to extract only the hours worked (8am -6pm) to complete the Task. My question is if, I am working only 10 hours in a day, then. This formula is giving me the hours: minutes: seconds properly when customized as [h]:mm:ss. if I custom the format d :hh:mm:ss, I get 1 day 23 hours 54 minutes 43 seconds. My requirement is 4 days 7 hours 54 minutes 47 seconds. am struggling to get figure out the formula. Please Can I request you to help me on this. Thank you

Time Spent column includes the total time including weekends. Its raw data

Sample data