Hi

I am trying to calculate the number of hours that 1 take to respond to a request.

I have been able to do it except for the row highlighted in red.


A1 B1 C1 D1
PSYU00006986 6/15/2010 9:00 6/28/10 13:00 122:00:00

FORMULA USED:
=(SUMPRODUCT(INT((WEEKDAY(B2-{2,3,4,5,6,7,1})+INT(C2)-INT(B2))/7),{12,12,12,12,12,5,0})-
SUMPRODUCT((AM$1:AM$10>B2)*(AM$1:AM$10< C2),LOOKUP(WEEKDAY(AM$1:AM$10),{1,6},{12,5}))+
(MOD(C2,1)-MOD(B2,1))*24+IF(WEEKDAY(B2)=1,2,8)-IF(WEEKDAY(C2)=8,13,20))/24


Please help.

From Mnday-Friday, working hours is 08:00-20:00 and on Saturday it is 08:00-13:00.

The incoming date/time is in B2 and reponsetime/date is in C2.

I have to calculate the working hours time taken in D2