Calculating Time Email received to Service Request opened

[pyrofenix]

I have a report that gives me the date & time an email was received to the date & time that my team created a service request. Most of the time the email is received during business hours, but there are instances where it will come over a weekend or after the team has left for the day. I am trying to calculate the number of hours:min (only during work hours) that the email took before a service request was created.

A1 = Date & Time Email received (eg: 2/1/2012 9:33 PM)
B1 = Date & Time Service Request opened (eg: 2/2/2012 8:17 AM)
C1 = Start Time (eg: 7:00 AM)
D1 = End Time (eg: 9:00 PM)
E1 = Work Hours in Hrs:Min (eg: 0:44)

The formula I was using is giving me mostly good results, but for some reason with the example data above. It is not accurate.

=IF(NETWORKDAYS(A3,B3)=1,(MIN(D3,MOD(B3,1))-MAX(C3,MOD(A3,1))),(NETWORKDAYS(A3,B3)-2)*(D3-C3)+(D3-MAX(MOD(A3,1),C3))+MIN(MOD(B3,1),D3)-C3)

I should be getting an answer of 1:17 but it is giving me an answer of 0:44. Any thoughts what I have done wrong?

 

[Special-K99]

Deleted...........

 

[barry houdini]

Hello pyrofenix, welcome to MrExcel, try this formula to accommodate any times/dates

=(NETWORKDAYS(A3,B3)-1)*(D3-C3)+IF(NETWORKDAYS(B3,B3),MEDIAN(MOD(B3,1),C3,D3),D3)-MEDIAN(NETWORKDAYS(A3,A3)*MOD(A3,1),C3,D3)

 

[pyrofenix]

Perfect, thanks a lot!