ExcelBanter - Total Time to Resolution

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - Total Time to Resolution (https://www.excelbanter.com/excel-worksheet-functions/207059-total-time-resolution.html)

Total Time to Resolution

I am trying to calculate the total time it takes to resolve an issue
within an organization that has a 24 x 5 work schedule during the
week
and 9-5 hours on the weekend. I want to make sure that the non-work
hours on the weekend are not calculated.

Does anyone have a formula for the example below?

Submitted Date Resolved Date
10/15/08 10:51:20 AM 10/25/08 02:54:32 PM

Thanks in advance,

LJ

Total Time to Resolution

LJ wrote:
I am trying to calculate the total time it takes to resolve an issue
within an organization that has a 24 x 5 work schedule during the
week
and 9-5 hours on the weekend. I want to make sure that the non-work
hours on the weekend are not calculated.

Does anyone have a formula for the example below?

Submitted Date Resolved Date
10/15/08 10:51:20 AM 10/25/08 02:54:32 PM

Thanks in advance,

LJ

Submitted Date in A2, Resolved Date in B2

=NETWORKDAYS(A2,B2)-IF(WEEKDAY(A2,2)<6,MOD(A2,1),0)-IF(WEEKDAY(B2,2)<6,1-MOD(B2,1),0)+(((INT(B2)-INT(A2)+1)-NETWORKDAYS(A2,B2))*8-IF(WEEKDAY(A2,2)5,(MOD(A2,1)-TIME(9,0,0))*24,0)-IF(WEEKDAY(B2,2)5,(TIME(17,0,0)-MOD(B2,1))*24,0))/24

Not sure if this is optimized, but it seems to work.