Excel 2007 : Networkdays not bringing in the final jours of a weekday b4 weekend

[Icehockey44]

Hi All,

I hope the title makes sense!!!

I have a formula based on Networkdays, but when a job is logged on a friday and actioned on a saturday we count the response time as when the job was logged (friday) to the end of that day, not using the saturday arrival time as that is classed as out of hours (overtime)

so the background data is hours worked Mon-Fri 8am - 5pm.

I have attaced an example, I need a formula that will count all instances of Mon- Fri 8am-5pm hours worked, along with when a job is logged on a weekday and actioned on a weekend, to count the hours 'til close of play on the friday, instead of giving me ########.

I would be most grateful of any answers.

cheers

Donnascenario 2 get work day hours.xlsx

[daddylonglegs]

Shouldn't the correct result be 7:19?

This formula will count all MF hours in the period defined by B10 and B11 for any start and end times/dates in A2 and B2, even weekends, evenings etc.

=(NETWORKDAYS(A2,B2)-1)*(B11-B10)+IF(NETWORKDAYS(B2,B2),MEDIAN(MOD(B2,1),B10,B11),B11)-MEDIAN(NETWORKDAYS(A2,A2)*MOD(A2,1),B10,B11)

[Icehockey44]

Hi daddylonglegs,

Thanks very much for this, has worked a treat. And event he other way, in that if the call was logged on the weekend and finished on a tuesday it counts from Monday to time finish. brilliant, saved me a huge amount of time.

cheers