Jun 08 2021 08:06 AM
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:
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.
Jun 08 2021 09:14 AM
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)
Jun 08 2021 09:54 AM
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?
Jun 08 2021 11:05 AM
SolutionThe 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...
Jun 08 2021 11:31 AM
Jun 08 2021 11:05 AM
SolutionThe 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...