Calculating average turnaround time and per day contact

[kjbill]

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.

[#2]

@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)

[#3]

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

[#4 · Accepted 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...

[#5]

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