Formula help for Net Work Hours

jahli

Board Regular
Joined
Oct 2, 2006
Messages
121
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.)
 

Some videos you may like

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
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

Board Regular
Joined
Oct 2, 2006
Messages
121
Looks like it is working... thank you so much... I will test both.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,514
Messages
5,602,091
Members
414,501
Latest member
mdhaumyu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top