Need help with function calculating networkdays between two date sets

Hello!

I am trying to figure out the correct formula to use to calculate the below data for a budget spendplan for calculating the number of work hours in a month:
I have a Period of Performance (PoP) as: 26Sep2013 through 28Feb2014 and need to calculate networkdays for every month.

I came up with the below formula where AD16 is 01Sep2013, AE16 is 30Sep2013, L21 is 26Sep2013, M21 is 28Feb2014. The "8" is work hours in a day.
=IF(AND(AD$16:AE$16>$L21,AD$16:AE$16<$M21),NETWORKDAYS(AD$16,AE$16)*8,0)

This formula works for whole months within the PoP i.e. 01Oct2013 thru 31Oct2013 but it will not work if it is the first month in the PoP such as 26Sep. It gives me a zero value so I need it to also calculate the networkdays that would be between 26Sep2013 and 30Sep2013.

Pic.jpg

Thanks so much for any help I can get!!!!

MM

Thanks so much for the reply but that won't work because I need it to do an IF function.

Thanks Tony! I see what you did and that would work if I only had one set PoP to cover but it changes. I've attached a jpeg of the actual file. I can't attached the Excel file because it has some company proprietary info in it. Let me explain a little better what I need the formula to do. I have a project that goes from 01March2013 through 28Feb2014. The project has numerous labor categories (LCAT) i.e. data analyst, project manager, acquisition specialist, etc and each LCAT has a different start date so some may start in May 2013 and others not until Sep or Oct 2013. I have the file setup to show the work hours and cost in each month. I have over 500 LCATs to project hours/costs for. So what I would like is to be able to type in the PoP start date (Column L) and PoP end date (Column M) and then all the "Hrs" columns to calculate the number of networkdays for that specific month. If the month does not fall into the PoP then it would return a "0". I plugged your formula above in the file but as you can see in March 2013 I'm getting -129. Need that to be zero (along with Apr-Aug) since the start date isn't until Sept. The formula I had originally used worked with the exception of it wouldn't calculate the first month of the PoP if it was a partial month i.e. start date was 26 Sep instead of 01 Sept.

I appreciate anyones help with this! My brain is an egg right now!

File1.jpg

MM

Yep...attached is a sample file of an end result. I would like to be able to type the start date and end date in columns C/D and then the Columns named "Hrs" will have a formula that will calculate the number of work hours in each month based on the start and end date in Column C/D. So for example, in E4 the end result would be zero since the start date is in September and E4 is the month of August. In G4, it would calculate the number of work hours in Sept. However, since the start date isn't until 26Sept, then it would know to calculate 26Sep-30Sep. In I4, it would calculate the whole month of Oct because Oct falls in between the start and end date. My original formula worked except for the month it began. So for line 4 in the sample, it would return a result of zero because it wasn't recognizing that there was a partial month.

THANK YOU!

MM

sample1.xls

Hi Tony! Your file is perfect! I should have said the hours in the sample file were not exact. I just did a swag to put hours in. But what you did worked perfect. Thanks so much for all the help! I really appreciate it!!!

MM