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 said:
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 said:
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......
:
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
:
=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?
