| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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? |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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? |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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? |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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? |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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? |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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? |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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? |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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? |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Extremely slow response times in Excel 2007 | Excel Discussion (Misc queries) | |||
| Displaying calculated times - half-hours are not calculating... | Excel Worksheet Functions | |||
| Date and Time Response- before/during/after Business hours | Excel Discussion (Misc queries) | |||
| Calculation of hourly rate times hours times 1.5 | Excel Worksheet Functions | |||
| Can Excel2002 determine if a calculated date is a business day? | Excel Worksheet Functions | |||
Linear Mode
