Prev: How do I insert a radio button using Excel 2007?
Next: Using Object & Worksheet names as variables
From: LABKHAND on 9 Apr 2010 13:21
All,

I am trying to figure out number of WORKING days per each week (1-6) of each
month excluding holidays. I have set up a sheet as follows:

B1=1/1/2010
then I have weeks#1 through 6 as a label in cells A3 through A8
in cell B3 I have the following formula:

=MIN(31-DAY(B$1+31),$A3*7-WEEKDAY(B$1))+1-SUM(B$2:B2) and the same formula
is dragged to populate cells B4 through B8.

I also have a named range defined for FY10_Holidays

The above formula works, but does not excludes hoildays from the total
number of days per week. Do you know how can I modify the formula to
exclude holidays?

Thanks for your help.
From: B Lynn B on 9 Apr 2010 13:30
check out the NETWORKDAYS function. I can't remember for sure, but this may
be one of the functions that pre-2007 versions of excel need you to install
the analysis toolpak.

"LABKHAND" wrote:

> All,
>
> I am trying to figure out number of WORKING days per each week (1-6) of each
> month excluding holidays. I have set up a sheet as follows:
>
> B1=1/1/2010
> then I have weeks#1 through 6 as a label in cells A3 through A8
> in cell B3 I have the following formula:
>
> =MIN(31-DAY(B$1+31),$A3*7-WEEKDAY(B$1))+1-SUM(B$2:B2) and the same formula
> is dragged to populate cells B4 through B8.
>
> I also have a named range defined for FY10_Holidays
>
> The above formula works, but does not excludes hoildays from the total
> number of days per week. Do you know how can I modify the formula to
> exclude holidays?
>
> Thanks for your help.
From: LABKHAND on 9 Apr 2010 13:35
B Lynn,

I know about the NETWORKDAYS function, my problem is that I do not know how
to modify my formula to use this function.


"B Lynn B" wrote:

> check out the NETWORKDAYS function. I can't remember for sure, but this may
> be one of the functions that pre-2007 versions of excel need you to install
> the analysis toolpak.
>
> "LABKHAND" wrote:
>
> > All,
> >
> > I am trying to figure out number of WORKING days per each week (1-6) of each
> > month excluding holidays. I have set up a sheet as follows:
> >
> > B1=1/1/2010
> > then I have weeks#1 through 6 as a label in cells A3 through A8
> > in cell B3 I have the following formula:
> >
> > =MIN(31-DAY(B$1+31),$A3*7-WEEKDAY(B$1))+1-SUM(B$2:B2) and the same formula
> > is dragged to populate cells B4 through B8.
> >
> > I also have a named range defined for FY10_Holidays
> >
> > The above formula works, but does not excludes hoildays from the total
> > number of days per week. Do you know how can I modify the formula to
> > exclude holidays?
> >
> > Thanks for your help.
From: Bob Phillips on 9 Apr 2010 14:12
Seems incredibly unwieldy, I will try and get a simplet formula, but add
this to your formula

-SUMPRODUCT(--(ISNUMBER(MATCH(holidays,ROW(INDIRECT(MIN(DATE(YEAR($B$1),MONTH($B$1)+1,0),($B$1+($A3-1)*7-WEEKDAY($B$1+($A3-1)*7)+1))&":"&MIN(DATE(YEAR($B$1),MONTH($B$1)+1,0),($B$1+($A3)*7-WEEKDAY($B$1+($A3)*7))))),0))))

--

HTH

Bob

"LABKHAND" <LABKHAND(a)discussions.microsoft.com> wrote in message
news:4145DFFA-D272-43F1-8867-0FFF4D92D175(a)microsoft.com...
> All,
>
> I am trying to figure out number of WORKING days per each week (1-6) of
> each
> month excluding holidays. I have set up a sheet as follows:
>
> B1=1/1/2010
> then I have weeks#1 through 6 as a label in cells A3 through A8
> in cell B3 I have the following formula:
>
> =MIN(31-DAY(B$1+31),$A3*7-WEEKDAY(B$1))+1-SUM(B$2:B2) and the same formula
> is dragged to populate cells B4 through B8.
>
> I also have a named range defined for FY10_Holidays
>
> The above formula works, but does not excludes hoildays from the total
> number of days per week. Do you know how can I modify the formula to
> exclude holidays?
>
> Thanks for your help.


From: B Lynn B on 9 Apr 2010 14:50
OK, sorry to have underestimated your familiarity with the available
functions. Can you please clarify one part of your question? "WORKING days
per each week (1-6)" - does that mean you're trying to count each week as
having 6 workdays? Otherwise I don't get what you mean by the "1-6".

"LABKHAND" wrote:

> B Lynn,
>
> I know about the NETWORKDAYS function, my problem is that I do not know how
> to modify my formula to use this function.
>
>
> "B Lynn B" wrote:
>
> > check out the NETWORKDAYS function. I can't remember for sure, but this may
> > be one of the functions that pre-2007 versions of excel need you to install
> > the analysis toolpak.
> >
> > "LABKHAND" wrote:
> >
> > > All,
> > >
> > > I am trying to figure out number of WORKING days per each week (1-6) of each
> > > month excluding holidays. I have set up a sheet as follows:
> > >
> > > B1=1/1/2010
> > > then I have weeks#1 through 6 as a label in cells A3 through A8
> > > in cell B3 I have the following formula:
> > >
> > > =MIN(31-DAY(B$1+31),$A3*7-WEEKDAY(B$1))+1-SUM(B$2:B2) and the same formula
> > > is dragged to populate cells B4 through B8.
> > >
> > > I also have a named range defined for FY10_Holidays
> > >
> > > The above formula works, but does not excludes hoildays from the total
> > > number of days per week. Do you know how can I modify the formula to
> > > exclude holidays?
> > >
> > > Thanks for your help.
 |  Next  |  Last
Pages: 1 2 3
Prev: How do I insert a radio button using Excel 2007?
Next: Using Object & Worksheet names as variables