Formula help for Net Work Hours

[jahli]

I'm wondering if someone has a formula that will compute "Net Work Hours". I use the =NETWORKDAYS formula to compute the number of days a project takes from start to finish but, when two projects take exactly the same number of hours, but the finish extends even 9 minutes into the next day the results differ by a day. For example:
Project____Start_______________Finish_____________Days to complete
Project A: "8/20/09 11:42 AM" to "9/1/09 12:56 PM" = 9.0 days
Project B: "8/20/09 11:55 AM" to "9/2/09 12:09 AM" = 10.0 days

(Sorry this matrix is hard to read.)

 

[barry houdini]

I don't think the two time spans are the same hours, the second one is 11 hours longer, isn't it?

Assuming you want to count all weekday hours.....and assuming your start and end points aren't at the weekend then try this formula to get weekday hours

=NETWORKDAYS(A2,B2)-1+MOD(B2,1)-MOD(A2,1)

format result cell as [h]:mm

where start time/date is in A2 and end time/date in B2

For your example that gives me 193:14 for the first and 204:14 for the second.

If you want it could be amended to show days and hours e.g.

=NETWORKDAYS(A2,B2)-1-(MOD(B2,1)< MOD(A2,1))&" days "&TEXT(B2-A2,"hh:mm")<MOD(A2,1))&AMP;" p days ?&TEXT(B2-A2,?hh:mm?)<>

 

[jahli]

Looks like it is working... thank you so much... I will test both.