Using networkdays to get total hours worked during a work week in a specific time everyday

[classichemp]

Hi,
I'm having difficultly using the networkdays formula to get the total hours worked over a certain span of days. Please see the attachment. Our total work day is 7:30 am to 4pm monday to friday. my formula in cell D2 i though would work but it gives 20 hours when it should be 12 hours. It also screws up when the time period is over the weekend, it adds more hours in when it shouldn't. The date format in cells A2 & B2 cannot be changed as its part of a large spreadsheet everyone uses.

Thanks for your help with this.

Formula used: =(NETWORKDAYS(A2,B2)-1)*(C9-C8)+IF(NETWORKDAYS(B2,B2),MEDIAN(MOD(B2,1),C9,C8),C9)-MEDIAN(NETWORKDAYS(A2,A2)*MOD(A2,1),C9,C8)