Hi all

i have a team who respond to customer requests, and they record when the
request came in and when it went out. I then report on how many working hours
they took to repsond, to see if within SLA or not.

To calculate this i use the following formula from Chip Pearson's website
http://www.cpearson.com/excel/datetimews.htm

=IF(AND(INT(StartDT)=INT(EndDT),NOT(ISNA(MATCH(INT (StartDT),HolidayList,0))))
,0,ABS(IF(INT(StartDT)=INT(EndDT),ROUND(24*(EndDT-StartDT),2),
(24*(DayEnd-DayStart)*
(MAX(NETWORKDAYS(StartDT+1,EndDT-1,HolidayList),0)+
INT(24*(((EndDT-INT(EndDT))-
(StartDT-INT(StartDT)))+(DayEnd-DayStart))/(24*(DayEnd-DayStart))))+
MOD(ROUND(((24*(EndDT-INT(EndDT)))-24*DayStart)+
(24*DayEnd-(24*(StartDT-INT(StartDT)))),2),
ROUND((24*(DayEnd-DayStart)),2))))))

Whe

StartDT The starting date and time
EndDT The ending date and time
DayStart The time of day that the normal work day begins
DayEnd The time of day that the normal work day ends.
HolidayList A range containing a list of dates to excluded, e.g., holidays
or vacation days.

this works very well for me, except for when the request is recieved after
the DayEnd on a Friday, or comes in during Saturday or Sunday. It then
calculates incorrectly.

Can anyone help with this, i've been going down nested if statements to try
and sort it, but suspect there must be a more logical way.

regards

Richard


--
Richard