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.