Response times calculated in business hours

[Guest]

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?

 

[Guest]

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:

 

[Guest]

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

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

Regards,
Stefi


„Nippy†ezt írta:

 

[Guest]

see:

http://groups.google.com/group/micr...nk=st&q="business+hours"+group:*excel&rnum=5#

 

[Guest]

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

 

[Guest]

Hi, in my opinion the response time is the time it took to respond to a call,
from the date of logging it. The only way to do this is to do the following
checks and calculate the time difference appropriately.
1. Whether reponse date is a week day i.e. on a Friday
2. Whether reponse time is at the end of a week day i.e. on a Friday

 

[Guest]

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......

 

[Guest]

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

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:

 

[Guest]

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

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

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?

 

[Guest]

That's perfect! Thank you very much.

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

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

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?