Excel 2007 : Processing Time that excludes holiday, weekends, and non-business hours

this is my first post so if you need more info just let me know.

I am trying to write a formula that will calculate the time between two dates/times. The clock should stop on all weekends and federal holiday. It should also stop at 5 PM and resume at 8 AM the next business day. If the second date is during a non-working day or hour, it should calculate as 5PM the previous business day

The following formula gets me part of the way but it seems to have a problem when the start date is on a holiday or weekend.

=IF(AND(INT(A5)=INT(B5),NOT(ISNA(MATCH(INT(A5),D$2:D$113,0)))),0,ABS(IF(INT(A5)=INT(B5),ROUND(24*(B5-A5),2),(24*(F$2-E$2)*(MAX(NETWORKDAYS(A5+1,B5-1,D$2:D$113),0)+INT(24*(((B5-INT(B5))-(A5-INT(A5)))+(F$2-E$2))/(24*($F$2-$E$2))))+MOD(ROUND(((24*(B5-INT(B5)))-24*E$2)+(24*F$2-(24*(A5-INT(A5)))),2),ROUND((24*(F$2-E$2)),2))))))

A=Application date
B=First activity date

D=List of excluded dates
E=Business day start time
F=Business day end time

I have attached a sample spreadsheet with the formula above.

Thanks for the help

Not clear if you want the results as true time values or in decimal hours.

In terms of true time, perhaps:

=MAX(0,(NETWORKDAYS($A5,$B5,$D$2:$D$113)-1)*($F$2-$E$2)+IF(NETWORKDAYS($B5,$B5,$D$2:$D$113),MEDIAN(MOD($B5,1),$F$2,$E$2),$F$2)-MEDIAN(NETWORKDAYS($A5,$A5,$D$2:$D$113)*MOD($A5,1),$F$2,$E$2))
copied down
format cell as [h]:mm:ss

if you want in terms of decimal hours (eg 1.5 for 1:30) multiply above result by 24.

Could you outline some examples which otherwise generated errors ?

(edit: I would expect that if for ex. start > end)