Response Rates

[Leonidas75]

Hello

Trying to work out response times between 2 timestamps between start of business say Monday 8AM and operate 24 hours to Friday 11PM and exclude hours outside of hours- ie. Weekends and Public Holidays. Note: Hours of operation will be 24 hours between Monday open and Friday close.

So if i receive a ticket on Monday at 9am and it is completed at 10am same day response time is 60 minutes. What i also need to determine is if a ticket is received at any time during the week and goes into the following week, i need to work out response times.

eg. ticket is received Friday 10pm and is resolved on Monday 10am, response time will be 180 minutes (takes into consideration last hour of business on Friday and first 2 hours of business Monday).

I am trying to find a suitable formula for all scenarios noted above.

Thanks

 

[steve the fish]

This may work. It will need testing by yourself.

=IF(B1>INT(A1)-WEEKDAY(A1+1)+TIME(23,0,0)+7,B1-A1-((2+TIME(9,0,0)*SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(INT(A1)&":"&INT(B1))))=6)))),B1-A1)*60*24

 

[Leonidas75]

This may work. It will need testing by yourself.

=IF(B1>INT(A1)-WEEKDAY(A1+1)+TIME(23,0,0)+7,B1-A1-((2+TIME(9,0,0)*SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(INT(A1)&":"&INT(B1))))=6)))),B1-A1)*60*24

Thanks i will give that a go....but for my understanding where is the calculation taking into consideration the 11pm close and 8am start on Monday.

Are you able to explain the mechanics of the formula if possible so i can get it right in my mind

I am assuming A1 is the ticket at 10pm and completion is B1.

 

[steve the fish]

Yes A1 is start and B1 is end. It first tests if B1 is greater than 11pm on the next friday. If so we know we have to remove the weekend hours. The 2 + Time(9,0,0) part is the weekend hours. That is 2 days plus the extra hours sum to 9. It then just tests how many weekends there are between the two dates and multiplies the weekend hours by this number. It makes a few assumptions. The two date times in A1 and B1 will always occur during working hours. B1 is greater than A1 and there are actually date times in both A1 and B1.

 

[Leonidas75]

Yes A1 is start and B1 is end. It first tests if B1 is greater than 11pm on the next friday. If so we know we have to remove the weekend hours. The 2 + Time(9,0,0) part is the weekend hours. That is 2 days plus the extra hours sum to 9. It then just tests how many weekends there are between the two dates and multiplies the weekend hours by this number. It makes a few assumptions. The two date times in A1 and B1 will always occur during working hours. B1 is greater than A1 and there are actually date times in both A1 and B1.

I see, so the calculation is already observing the business 24 hours of 08:00 Monday through to Friday 23:00 from the 2 + Time(9,0,0).

You raised another point here to consider.....what about if the ticket is delivered outside of hours.....say Saturday 9am and is completed on Tuesday 11am.

 

[steve the fish]

You would need to add that condition in but its tricky. But i havent got time as im off out now.

 

[Toadstool]

Yes A1 is start and B1 is end. It first tests if B1 is greater than 11pm on the next friday. If so we know we have to remove the weekend hours. The 2 + Time(9,0,0) part is the weekend hours. That is 2 days plus the extra hours sum to 9. It then just tests how many weekends there are between the two dates and multiplies the weekend hours by this number. It makes a few assumptions. The two date times in A1 and B1 will always occur during working hours. B1 is greater than A1 and there are actually date times in both A1 and B1.

I've been working on the same approach but struggling with Public Holidays, especially differentiating those that abut a weekend (most) and those that don't.