Response times calculated in business hours
 

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?

Response times calculated in business hours
 

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?

Response times calculated in business hours
 

=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?

Response times calculated in business hours
 

see:

http://groups.google.com/group/micro...*excel&rnum=5#


--
Gary's Student
gsnu200706


"Nippy" wrote:

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?

Response times calculated in business hours
 

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?

Response times calculated in business hours
 

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

"Nippy" wrote:

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?

Response times calculated in business hours
 

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?

Response times calculated in 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

"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?

Response times calculated in business hours
 

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?

Response times calculated in business hours
 

That's perfect! Thank you very much.

"daddylonglegs" wrote:

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?