Hello Scott,

I didn't see your post until now....

Try this formula:


=(NETWORKDAYS(A1,B1)-1)*(H$3-H$2)+IF(NETWORKDAYS(B1,B1),MEDIAN(MOD(B1,1),H$3,H$ 2),H$3)-MEDIAN(NETWORKDAYS(A1,A1)*MOD(A1,1),H$3,H$2)

formatted as [h]:mm

where A1 contains start time/date, B1 contains end time/date, H2 contains
your Monday to Friday start time e.g. 08:30 and H3 Monday to Friday end time
e.g. 17:00. You can even exclude holidays, if you have a list of these and
use as the third argument of each instance of NETWORKDAYS

This will calculate business hours between the start and end, even if start
and end times are outside business hours

"Scopar" wrote:

Hi,

I have a similar situation, but it's for tracking business hours to respond
to e-mails and since e-mails are received outside of normal business hours, I
receive an error message for those dates/times.

I can manually adjust these dates and times so that an e-mail received on a
Saturday or Sunday is considered to be received at 8:30 the following Monday
morning, but it would be handy to have a formula that could take care of it
for me ... I'm always looking for the easy way out :)

Thanks,
Scott

"daddylonglegs" wrote:

Assuming both call log time/date and "responded by" time/date are always
within business hours, this will give total business hours

=(NETWORKDAYS(A1,B1)-1)*("17:00"-"09:00")+MOD(B1,1)-MOD(A1,1)

format result cell as [h]:mm

NETWORKDAYS requires Analysis ToolPak, to install, Tools Addins tick
Analysis ToolPak box

If you want a formula which works for times outside business hours or one
that avoids using NETWORKDAYS post back......

"Stefi" wrote:

Sorry, I forgot to mention that it's part of the Analysis Toolpak add-in,
it's present in XL2003. Tools/Add-Ins/Check Analysis Toolpak!

Regards,
Stefi


€žNippy€� ezt Ã*rta:

Is NETWORKDAYS a 2007 feature? it does not seem to be a function in 2003

"Stefi" wrote:

=B2-A2-NETWORKDAYS(A2,B2)-16*1/24

Not fully tested, but gives the correct result in your example.

Regards,
Stefi


€žNippy€� ezt Ã*rta:

Hello, i wish to calculate response times in business hours.

I have two columns of data; A represents the "call log" date and time; B
represents the "responded by" date and time.

I wish to calculate the time difference between A & B, but only in office
hours.

As an example; Office hours 9am to 5 pm Mon-Fri (no sat or Sun)
A call received at 4pm on friday that was dealt with by 10am on monday would
have a Response time of 2 hours.

Is there a neat way of doing this?