SOLVED

Calculating average turnaround time and per day contact

New Contributor

Hi all!

 

I am struggling with calculating turnaround time when it comes to communications coming in and being answered on a weekend or holiday.

Platform: Microsoft Excel for Mac (version 16.49)

Current formula:

=NETWORKDAYS(A2,B2,$E$2:$E$17)+IF(AND(MOD(WEEKDAY(B2),7)>1,ISERROR(MATCH(B2,$E$2:$E$17,0))),MOD(B2,1)-1,0)-IF(AND(MOD(WEEKDAY(A2),7)>1,ISERROR(MATCH(A2,$E$2:$E$17,0))),MOD(A2,1),0)

Context:

  • Column A holds the start time (e.g. 5-3-21 11:35 AM)
  • Column B holds the end time (e.g. 5-3-21 9:17 PM)
  • Column C holds result of B-A = ########### (which I am assuming is a negative value)
  • Column E holds the list of holiday days, May 3rd being one of them

I could also include working hours in the formula but a large number of our communications occur outside working hours. I  don't want to skew the numbers with this detail. I am happy to stick with Monday - Friday being calculated at 24 hours per day.

 

Any advice that could be provided would be a huge help. Ultimately, I need to calculate average response time (turnaround) as well as the # of communications handled per working day each month.

I have attached a sample workbook.

4 Replies

@kjbill 

Try this:

 

=MAX(NETWORKDAYS(A17,B17,$E$2:$E$17)-1,0)+IF(AND(MOD(WEEKDAY(B17),7)>1,ISERROR(MATCH(B17,$E$2:$E$17,0))),MOD(B17,1),0)-IF(AND(MOD(WEEKDAY(A17),7)>1,ISERROR(MATCH(A17,$E$2:$E$17,0))),MOD(A17,1),0)

@Hans Vogelaar 

Thank you so much for your response!!

 

This has gotten me a little closer.

This formula is now calculating when the start & end times are on the 'days off'. However, I am still getting a negative value when the start date is 2021-05-03 3:41:00 PM but the response date is 2021-05-04 1:02:00 PM.

 

Any suggestions on this one?

best response confirmed by kjbill (New Contributor)
Solution

@kjbill 

The attached workbook (now a .xlsm, so you'll have to allow macros) contains a custom function WorkingHours.

It completely ignores weekend days and holidays, I'm not sure that it what you wanted...

Again, thank you for this fantastic response. This looks like it is going to do what I need it to.