calculating total time worked to measure SLA

I am trying to determine the SLA performance of our help desk. I have a formula that seems to work well with calculating total hours based on working days and hours, except in cases where the issue is closed after standard work hours. Entries submitted earlier is ok as we allow email and self-service-portal submissions, it is rare that we have an early morning emergency. However not calculating the correct total time (completed later than standard work end time) is less than desired.

Example:
Standard work start time: 08:00
Standard work stop time: 17:00

Submitted: 7/30/2010 16:44
Completed: 8/02/2010 17:35
Time calculated using formula below: 9:15
Actual work time: 9:50

Submitted: 08/2/2010 15:15
Completed: 08/2/2010 17:36
Time calculated using formula below: 1:44
Actual work time: 2:20

This is the formula that I am using:
"=(NETWORKDAYS(D6,E6)-1)*E$2-$2)+IF(NETWORKDAYS(E6,E6),MEDIAN(MOD(E6,1),E$2,D$2),E$2)-MEDIAN(NETWORKDAYS(D6,D6)*MOD(D6,1),E$2,D$2)"

D6 = Start Date/Time
E6 = End Date/Time
D$2 = Std work start time
E$2 = Std work end time

I am not sure if I can achieve this goal with a single, any solutions suggested will be appreciated.

TIA,
Bill

This worked great, thank you!

Just curious, do you think it would be simpler to try and specify 8 hour days as opposed to start and stop times? I copied the original formula from somebody trying to calculate a time card. I am trying to calculate submit to resolution hours using 8 hour workdays for dates in between submit and close.

Thank you!

Sorry for the long delay. Your previous suggestion corrected my issue, thank you.

I had originally used a formula that I found from somebody calculating a timesheet. I thought that perhaps I had made it more difficult than necessary because I am only trying to calculate start to finish time based on an 8 hour workday/ 5 day work week.

For the past month I have been trying to figure out if it would be practical to take this to a higher level of accuracy. However I have concluded that there are simply too many conditions to achieve this.

Here is the list I created before stopping...
Submit during Std Hrs - Work Day, Resolved during Std Hrs - Same date, Work Day
Submit during Std Hrs - Non-Work Day, Resolved during Std Hrs - Same date, Non-Work Day
Submit during Std Hrs - Work Day, Resolved during Std Hrs - Later date, Work Day
Submit during Std Hrs - Work Day, Resolved during Std Hrs - Later date, Non-Work Day
Submit during Std Hrs - Non-Work Day, Resolved during Std Hrs - Later date, Work Day
Submit during Std Hrs - Non-Work Day, Resolved during Std Hrs - Later date, Non-Work Day
Submit BEFORE Std Hrs - Work Day, Resolved during Std Hrs - Same date, Work Day
Submit BEFORE Std Hrs - Non-Work Day, Resolved during Std Hrs - Same date, Non-Work Day
Submit BEFORE Std Hrs - Work Day, Resolved during Std Hrs - Later date, Work Day
Submit BEFORE Std Hrs - Work Day, Resolved during Std Hrs - Later date, Non-Work Day
Submit BEFORE Std Hrs - Non-Work Day, Resolved during Std Hrs - Later date, Work Day
Submit BEFORE Std Hrs - Non-Work Day, Resolved during Std Hrs - Later date, Non-Work Day
Submit BEFORE Std Hrs - Work Day ,Resolved AFTER Std Hrs - Same date, Work Day
Submit BEFORE Std Hrs - Non-Work Day, Resolved AFTER Std Hrs - Same date, Non-Work Day
Submit BEFORE Std Hrs - Work Day, Resolved AFTER Std Hrs - Later date, Work Day
Submit BEFORE Std Hrs - Work Day, Resolved AFTER Std Hrs - Later date, Non-Work Day
Submit BEFORE Std Hrs - Non-Work Day, Resolved AFTER Std Hrs - Later date, Work Day
Submit BEFORE Std Hrs - Non-Work Day, Resolved AFTER Std Hrs - Later date, Non-Work Day
Submit AFTER Std Hrs - Work Day, Resolved AFTER Std Hrs - Same date, Work Day
Submit AFTER Std Hrs - Work Day, Resolved during Std Hrs - Later date, Work Day
Submit AFTER Std Hrs - Work Day, Resolved AFTER Std Hrs - Later date, Work Day
Submit AFTER Std Hrs - Work Day, Resolved AFTER Std Hrs - Later date, Non-Work Day
Submit AFTER Std Hrs - Non-Work Day, Resolved AFTER Std Hrs - Later date, Non-Work Day
Submit AFTER Std Hrs - Non-Work Day, Resolved AFTER Std Hrs - Later date, Non-Work Day


Thank you again for your help, I greatly appreciate it!

Cheers,
Bill