NetWorkDays based on Date Range 1

[YANKRAY]

Excel 2003.

I am trying to determine number of work days per month for a projects duration.

For Example: Project Starts 6 Jun 2007, Project Ends 21 December 2007.

Is there a formula I can use that will put the network days under the column or row heading for the month? The result would look like this:

May 07 0
Jun 07 4
Jul 07 22
Aug 07 23
Sep 07 20
Oct 07 23
Nov 07 22
Dec 07 10 (Only 10 work days because the date of the report is the 10th so work days 3-7 are already past). The current month should always be the remaining work days.
Jan 08 0

I will use a seperate spreadsheet to record holidays for 2007, 2008, 2009, etc.

Thanks,
Ray

 

[SkipVought]

Hi,

Where do you get 4 in June. It should be 18.

Use a forumla like this...
[tt]
B1: =IF(OR(A2-1<Strt,A9>End),0,NETWORKDAYS(A1,A2-1,Holidays))
[/tt]
where A1:A9 is...
[tt]
May-07
Jun-07
Jul-07
Aug-07
Sep-07
Oct-07
Nov-07
Dec-07
Jan-08
[/tt]
and Holidays is a named range like this...
[tt]
Holidays
6/1/2007
6/2/2007
6/3/2007
6/4/2007
6/5/2007
12/22/2007
12/23/2007
12/24/2007
12/25/2007
12/26/2007
12/27/2007
12/28/2007
12/29/2007
12/30/2007
12/31/2007
[/tt]
that is a list of excluded dates in the partial moinths.


Skip,
[sub]
When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[/sub]

 

[YANKRAY]

Skip,

Sorry about the bad number for Jun.

I am getting desired results except for Jun, Dec, and Jan 08.
Jun is producing a result of 0.
Dec is producing a result of 15.
(Dec should be only the remaining 10 work days)
(This probably can't be done in the same formula, but it would make things easier).
Jan 08 is producing #NUM!.

My Month fields are:
A105 = May-07 (which is the formated date 5/1/2007)
A106 = Jun-07
A107 = Jul-07
A108 = Aug-07
A109 = Sep-07
A110 = Oct-07
A111 = Nov-07
A112 = Dec-07
A113 = Jan-08

The Start Date is cell M2 and = 6/1/2007.
The Finish Date is cell and = 12/13/2007

The Cell formula's are as follows:

B105:
=IF(OR(A105-1<$M$2,$A$112>$N$2),0,NETWORKDAYS(A105,A106-1,Holidays!$A$2:$A$29))

B106:
=IF(OR(A106-1<$M$2,$A$112>$N$2),0,NETWORKDAYS(A106,A107-1,Holidays!$A$2:$A$29))

B107:
=IF(OR(A107-1<$M$2,$A$112>$N$2),0,NETWORKDAYS(A107,A108-1,Holidays!$A$2:$A$29))

B108:
=IF(OR(A108-1<$M$2,$A$112>$N$2),0,NETWORKDAYS(A108,A109-1,Holidays!$A$2:$A$29))

B109:
=IF(OR(A109-1<$M$2,$A$112>$N$2),0,NETWORKDAYS(A109,A110-1,Holidays!$A$2:$A$29))

B110:
=IF(OR(A110-1<$M$2,$A$112>$N$2),0,NETWORKDAYS(A110,A111-1,Holidays!$A$2:$A$29))

B111:
=IF(OR(A111-1<$M$2,$A$112>$N$2),0,NETWORKDAYS(A111,A112-1,Holidays!$A$2:$A$29))

B112:
=IF(OR(A112-1<$M$2,$A$112>$N$2),0,NETWORKDAYS(A112,A113-1,Holidays!$A$2:$A$29))

B113:
=IF(OR(A113-1<$M$2,$A$112>$N$2),0,NETWORKDAYS(A113,A114-1,Holidays!$A$2:$A$29))

Holidays are:
1-Jan-07
2-Jan-07
6-Apr-07
1-Jun-07
2-Jun-07
3-Jun-07
4-Jun-07
5-Mar-07
28-May-07
4-Jul-07
3-Sep-07
22-Nov-07
23-Nov-07
24-Dec-07
25-Dec-07
26-Dec-07
27-Dec-07
28-Dec-07
31-Dec-07
1-Jan-08
21-Mar-08
26-May-08
4-Jul-08
1-Sep-08
27-Nov-08
28-Nov-08
24-Dec-08
25-Dec-08

My Results are:
May-07 0
Jun-07 0
Jul-07 21
Aug-07 23
Sep-07 19
Oct-07 23
Nov-07 20
Dec-07 15
Jan-08 #NUM!

I am expecting to see:
May-07 0
Jun-07 18
Jul-07 21
Aug-07 23
Sep-07 19
Oct-07 23
Nov-07 20
Dec-07 10 (there are only 10 net work days remaining)
Jan-08 0

 

[SkipVought]

Here are my results
[tt]
May-07 0
Jun-07 18
Jul-07 22
Aug-07 23
Sep-07 20
Oct-07 23
Nov-07 22
Dec-07 6
Jan-08
[/tt]
I point to a different exclusion list for DEC...
[tt]
B8: =IF(OR(A9-1<Strt,A19>End),0,NETWORKDAYS(A8,A9-1,This_month))
[/tt]
where This_Month is...
[tt]
This month
12/11/2007
12/12/2007
12/13/2007
12/14/2007
12/15/2007
12/16/2007
12/17/2007
12/18/2007
12/19/2007
12/20/2007
12/21/2007
12/22/2007
12/23/2007
12/24/2007
12/25/2007
12/26/2007
12/27/2007
12/28/2007
12/29/2007
12/30/2007
12/31/2007
[/tt]


Skip,
[sub]
When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[/sub]

 

[YANKRAY]

I agree with your results. Some of my difference are because I have some additional Holidays included.

I can't figure out why my formula for Jun produces 0 instead of 18.

I see where you get 6 for Dec (6 is the number of work days completed).

For the current month the formula is doing a calculation, I need to see the remaining working days from the current date. The End date is Dec 21 so the remaining work days are the 10th to the 14th and the 17th to the 21st. This would be 10 days.

 

[SkipVought]

Your formula
[tt]
B105:
=IF(OR(A105-1<$M$2,$A$112>$N$2),0,NETWORKDAYS(A105,A106-1,Holidays!$A$2:$A$29))
[/tt]
should be
[tt]
=IF(OR(A10[red]6[/red]-1<Start,[red]A105[/red]>Finish),0,NETWORKDAYS(A105,A106-1,Holidays))
[/tt]

Skip,
[sub]
When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[/sub]

 

[YANKRAY]

Got it working now. Thanks for all your help.

Ray