Response Log

[mayoung726]

I'm trying to find a formula I can use to figure response time. The working hours are 7:00 AM to 3:00 PM and I do not want to count the hours between 3:00 pm and 7:00 AM of the next day.

Example:

Cell A1 has the call in DATE 12/06/07 and cell B1 has the call in time of 10:00 AM.

Cell C1 has the response date of 12/07/07 and cell D1 has the time of 9:00 AM.

So what I need is a formula in cell E1 that would count from 10:00AM untill 3:00 PM on 12/6/07 then pickup the count on 12/07/07 at 7:00 AM and go untill 9:00 AM.

So in this Example the final response time would be 7 hours or 7:00.


If anyone can help I sure would appreciate it

 

[AnAnalyst]

Okay not too difficult for the example you give - however a few questions!

1) Are response times sometimes within a single day?
2) Can a response span more than two days?
3) Do you need to take account of Saturdays and Sundays?

To account for the scenario you presented paste the following into cell E1:

=TEXT((A1+15/24)-(A1+B1)+(C1+D1)-(C1+7/24),"[h]:mm")

... but i get the feeling you may want to account for more variability in your underlying data.

 

[mayoung726]

Yes, you are correct all three variables could apply. But we try to respond the same day if not the next, but the variables you named could apply. So does this code need modified for these variables.

 

[mayoung726]

Yes, you are correct all three variables could apply. But we try to respond the same day if not the next, but the variables you named could apply. So does this code need modified for these variables.

 

[AnAnalyst]

Yes Mark, the formula would need to be changed to account for the different scenarios and a combination of possible inputs.

You will need to describe exactly what you want with regard to the working patterns - are we to assume that Saturdays and Sundays are not work days? Is the work carried out on public holidays? ... and so on.

 

[mayoung726]

We are a service company which could provide service 24 hours a day 365 days a year. Could include weekends or Holidays. I really appreciate your help! But normally the scenario is as described at the first posting but if you could add these variables it could help.
__________________

 

[mayoung726]

I just talked to our senior VP/COO and he said what they are looking at is just Monday through Friday 7:00 AM to 3:00 PM not to worry about night, weekends or Holidays.

The only sceniero I can think of is if we are called on Friday and responed on Monday. We would want to exclude the time from 3:00 PM on Friday to 7:00 AM on Monday. But say if we do not respond until Tuesday We would want to exclude the time from 3:00 PM on Friday to 7:00 AM on Tuesday. Hopefully this helps.

 

[AnAnalyst]

Hi - sorry, been away from computers for a few days. Getting withdrawal symptoms!

In your last post you imply that you want the formula to account for a gap between Friday and Tuesday - what if Monday is also a work day?

 

[AnAnalyst]

Okay ...

You must have the Analysis Toolpack enabled for this formula to function.

Say start date is in A2, start time in B2, end date in C2 and end time in D2. Paste the following formula into E2:

=IF(OR(A2="",B2="",C2="",D2=""),"",IF(A2=C2,D2-B2,((NETWORKDAYS(A2,C2)-2)*8/24)+(15/24-B2)+(D2-7/24)))

Make sure you format E2 to show correctly - I would suggest the custom format [h]:mm.

The first part of the formula =IF(OR(A2="",B2="",C2="",D2=""),"" checks to see if any of the relevant cels are blank and, if so, the calculation is not performed.

The second part IF(A2=C2,D2-B2 checks if the work starts and finishes on the same day and, if so, just calculates the difference between the start and finish times.

The third part ((NETWORKDAYS(A2,C2)-2)*8/24)+(15/24-B2)+(D2-7/24))) looks for the number of work days Monday to Friday (if there are more than one) and calculates 8 hours per day except it picks out only the time elapsed between 7:00 (7/24) and 15:00 (15/24) on the start and end dates.

If you want to exclude national or public holidays then you coud place the dates of those in an additional range (named Holidays maybe) on your spreadsheet and add in an extra argument in the NETWORKDAYS function so the formula will read as follows:

=IF(OR(A2="",B2="",C2="",D2=""),"",IF(A2=C2,D2-B2,((NETWORKDAYS(A2,C2,Holidays)-2)*8/24)+(15/24-B2)+(D2-7/24)))

Is this close to what you really wanted?

 

[barry houdini]

=IF(OR(A2="",B2="",C2="",D2=""),"",IF(A2=C2,D2-B2,((NETWORKDAYS(A2,C2,Holidays)-2)*8/24)+(15/24-B2)+(D2-7/24)))

You could simplify this to

=IF(COUNT(A2:D2)=4,(NETWORKDAYS(A2,C2,Holidays)-1)*8/24+D2-B2,"")

Note: this assumes that start and end times will always be within the working hours, i.e. 07:00 - 15:00 on work days

result cell to be formatted as [h]:mm