Time during business hours (week days) and time during Out of Hours (weekdays+weekends)

Hi Excel Forum

Need help calculating time spent between two dates:

1) Calculate the time spent during business hours (mon-fri, 09:00-17:00) and,

2) Calculate the time spent during non-business hours (mon-fri, 17:00-09:00 + sat-sun, 24/7).


Assume I need networkdays, if and other functions. But I am stuk :'(

Hope someone are able to help! Please see attached example.

Something like this?

=(NETWORKDAYS(B2,C2)-1)*("17:00"-"9:00")+IF(NETWORKDAYS(C2,C2),MEDIAN($H$4,"9:00","17:00"),"17:00")-MEDIAN(NETWORKDAYS(B2,B2)*$H$3,"9:00","17:00")

Hi Timbo1957

Yes, something like that. But does not quite solve it. About "MEDIAN($H$4,"9:00","17:00")" - Is that right?