SOLVED

New Contributor

# Calculating average turnaround time and per day contact

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

# Re: Calculating average turnaround time and per day contact

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)

# Re: Calculating average turnaround time and per day contact

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

# Re: Calculating average turnaround time and per day contact

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...

# Re: Calculating average turnaround time and per day contact

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