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.

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.

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.

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(YEA R($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" wrote in message
...
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.

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.

B Lynn,

I am trying to come up with the following data. If i enter 1/1/2010 in cell
B1 then the working days per week for this month should be:

WK#1 (0 working days since 1/2 was a holiday)
WK#2 (5 working days)
WK#3 (5 working days)
WK#4 (4 working days since 1/18 was a holiday)
WK#5 (5 working days)
WK#6 (0 working days)

I hope you see what I am doing. THX

"B Lynn B" wrote:

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.

Bob,

I tried your formula but it is not working since I get 1 working day for the
second week of Jan 2010. Thanks

"Bob Phillips" wrote:

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(YEA R($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" wrote in message
...
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.


.

I get 2 7 7 7 7 1 for January.

--

HTH

Bob

"LABKHAND" wrote in message
...
Bob,

I tried your formula but it is not working since I get 1 working day for
the
second week of Jan 2010. Thanks

"Bob Phillips" wrote:

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(YEA R($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" wrote in message
...
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.


.

LABKHAND;695295 Wrote:

B Lynn,

I am trying to come up with the following data. If i enter 1/1/2010 in
cell
B1 then the working days per week for this month should be:

WK#1 (0 working days since 1/2 was a holiday)
WK#2 (5 working days)
WK#3 (5 working days)
WK#4 (4 working days since 1/18 was a holiday)
WK#5 (5 working days)
WK#6 (0 working days)

I hope you see what I am doing. THX





You say:"WK#1 (0 working days since 1/2 was a holiday)"

Since the 2nd January 2010 was a Saturday, does this mean that your
working week is NOT normally Mon-Fri?

If it is Mon-Sat, the following will not work since it uses Excel's
Networkdays function which excludes Sat and Sun from the working week.

Hopefully it was a typo for "1/1 was a holiday" in which case the
following gives the same results. You use a formula like this in B3:

=WorkingDays(YEAR($B$1),1,A3,FY10_Holidays)

and copy down.

It works similarly to Networkdays in that the last argument
(FY10_Holidays) can be left out.
The first argument is the year, either use '2010' or as I've done here,
calculated the year from cell B1

The second argument is the month (1 to 12), or again you can calculate
it from a date elsewhere.
The third argument is the week number in the month (1 to 6) or again,
you can get that number from another cell.

Now none of this will work without the user-defined function behind it,
that is, until you put the following code into a standard code module:


VBA Code:
--------------------



Function WorkingDays(TheYear, TheMonth, TheMonthWkNo, Optional Holidays)
WorkingDays = "Error!"
On Error GoTo LeaveNow
Dim StartDate As Date, EndDate As Date
WeekNumofDay1ofTheMonth = Application.WorksheetFunction.WeekNum(DateSerial(T heYear, TheMonth, 1))
WeekNumofLastDayofTheMonth = Application.WorksheetFunction.WeekNum(DateSerial(T heYear, TheMonth + 1, 0))
WeekNumsInTheMonth = WeekNumofLastDayofTheMonth - WeekNumofDay1ofTheMonth + 1
If TheMonthWkNo <= WeekNumsInTheMonth Then 'checks for a non-existent week number
ActualWeekNo = TheMonthWkNo + WeekNumofDay1ofTheMonth - 1
myDate = DateSerial(TheYear, TheMonth, 0)
Do
myDate = myDate + 1
Loop Until Application.WorksheetFunction.WeekNum(myDate) = ActualWeekNo And Month(myDate) = TheMonth
StartDate = myDate
Do While Application.WorksheetFunction.WeekNum(myDate) = ActualWeekNo And Month(myDate) = TheMonth
myDate = myDate + 1
Loop
EndDate = myDate - 1
If IsMissing(Holidays) Then
WorkingDays = Application.WorksheetFunction.NetworkDays(StartDat e, EndDate)
Else
WorkingDays = Application.WorksheetFunction.NetworkDays(StartDat e, EndDate, Holidays)
End If
End If
LeaveNow:
End Function

--------------------


--
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: 558
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=194416

http://www.thecodecage.com/forumz

Ho Bob,

Can I send you my spreadsheet? If you do not mind of course so that you can
see the result I am getting. Please email me at so that I
send you this spreadsheet. Thanks very much for your responds.

"Bob Phillips" wrote:

I get 2 7 7 7 7 1 for January.

--

HTH

Bob

"LABKHAND" wrote in message
...
Bob,

I tried your formula but it is not working since I get 1 working day for
the
second week of Jan 2010. Thanks

"Bob Phillips" wrote:

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(YEA R($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" wrote in message
...
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.


.



.

p45cal,

Thanks, yes, It was a typo and I meant that 1/1/2010 was a holiday. Your
solution works partially. for January, I copied your instructions and it
gives me the right answers. But when I try to copy these to the adjescent
cells Excel hangs and I kill it.
I also tried copying the "=WorkingDays(YEAR($B$1),1,A3,FY10_Holidays)" to
adjesent cell (C3) in which i got an "ERROR!" msg in teh cell. What is
wrong?

"p45cal" wrote:


LABKHAND;695295 Wrote:

B Lynn,

I am trying to come up with the following data. If i enter 1/1/2010 in
cell
B1 then the working days per week for this month should be:

WK#1 (0 working days since 1/2 was a holiday)
WK#2 (5 working days)
WK#3 (5 working days)
WK#4 (4 working days since 1/18 was a holiday)
WK#5 (5 working days)
WK#6 (0 working days)

I hope you see what I am doing. THX





You say:"WK#1 (0 working days since 1/2 was a holiday)"

Since the 2nd January 2010 was a Saturday, does this mean that your
working week is NOT normally Mon-Fri?

If it is Mon-Sat, the following will not work since it uses Excel's
Networkdays function which excludes Sat and Sun from the working week.

Hopefully it was a typo for "1/1 was a holiday" in which case the
following gives the same results. You use a formula like this in B3:

=WorkingDays(YEAR($B$1),1,A3,FY10_Holidays)

and copy down.

It works similarly to Networkdays in that the last argument
(FY10_Holidays) can be left out.
The first argument is the year, either use '2010' or as I've done here,
calculated the year from cell B1

The second argument is the month (1 to 12), or again you can calculate
it from a date elsewhere.
The third argument is the week number in the month (1 to 6) or again,
you can get that number from another cell.

Now none of this will work without the user-defined function behind it,
that is, until you put the following code into a standard code module:



VBA Code:
--------------------




Function WorkingDays(TheYear, TheMonth, TheMonthWkNo, Optional Holidays)
WorkingDays = "Error!"
On Error GoTo LeaveNow
Dim StartDate As Date, EndDate As Date
WeekNumofDay1ofTheMonth = Application.WorksheetFunction.WeekNum(DateSerial(T heYear, TheMonth, 1))
WeekNumofLastDayofTheMonth = Application.WorksheetFunction.WeekNum(DateSerial(T heYear, TheMonth + 1, 0))
WeekNumsInTheMonth = WeekNumofLastDayofTheMonth - WeekNumofDay1ofTheMonth + 1
If TheMonthWkNo <= WeekNumsInTheMonth Then 'checks for a non-existent week number
ActualWeekNo = TheMonthWkNo + WeekNumofDay1ofTheMonth - 1
myDate = DateSerial(TheYear, TheMonth, 0)
Do
myDate = myDate + 1
Loop Until Application.WorksheetFunction.WeekNum(myDate) = ActualWeekNo And Month(myDate) = TheMonth
StartDate = myDate
Do While Application.WorksheetFunction.WeekNum(myDate) = ActualWeekNo And Month(myDate) = TheMonth
myDate = myDate + 1
Loop
EndDate = myDate - 1
If IsMissing(Holidays) Then
WorkingDays = Application.WorksheetFunction.NetworkDays(StartDat e, EndDate)
Else
WorkingDays = Application.WorksheetFunction.NetworkDays(StartDat e, EndDate, Holidays)
End If
End If
LeaveNow:
End Function


--------------------


--
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: 558
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=194416

http://www.thecodecage.com/forumz

.

Well, it's difficult to say. Examine the formula in the new cell by
selecting it and pressing F2, this will highlight the cells it's
referring to. Are they the cells you expect?

Also:
a slight change in the function to the line:


VBA Code:
--------------------


If TheMonthWkNo <= WeekNumsInTheMonth Then
--------------------



to:


VBA Code:
--------------------


If TheMonthWkNo <= WeekNumsInTheMonth And TheMonthWkNo 0 Then
--------------------



will make it return an error rather than a misleading result if someone
tries to use the zeroth week of the month.



LABKHAND;696872 Wrote:

p45cal,

Thanks, yes, It was a typo and I meant that 1/1/2010 was a holiday.
Your
solution works partially. for January, I copied your instructions and
it
gives me the right answers. But when I try to copy these to the
adjescent
cells Excel hangs and I kill it.
I also tried copying the "=WorkingDays(YEAR($B$1),1,A3,FY10_Holidays)"
to
adjesent cell (C3) in which i got an "ERROR!" msg in teh cell. What
is
wrong?




--
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: 558
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=194416

http://www.thecodecage.com/forumz

p45cal,

IT WORKED!!! It was my mistake since the month number was a constant as
part of your formula and I was not changing it for the other columns. I
fixed it now. Thanks for all your help.

I send yo u an email with a second question I had. Would you be able to
help me out on that? Did you see an email from me?

Thanks


"p45cal" wrote:


Well, it's difficult to say. Examine the formula in the new cell by
selecting it and pressing F2, this will highlight the cells it's
referring to. Are they the cells you expect?

Also:
a slight change in the function to the line:



VBA Code:
--------------------



If TheMonthWkNo <= WeekNumsInTheMonth Then

--------------------



to:



VBA Code:
--------------------



If TheMonthWkNo <= WeekNumsInTheMonth And TheMonthWkNo 0 Then

--------------------



will make it return an error rather than a misleading result if someone
tries to use the zeroth week of the month.




LABKHAND;696872 Wrote:

p45cal,

Thanks, yes, It was a typo and I meant that 1/1/2010 was a holiday.
Your
solution works partially. for January, I copied your instructions and
it
gives me the right answers. But when I try to copy these to the
adjescent
cells Excel hangs and I kill it.
I also tried copying the "=WorkingDays(YEAR($B$1),1,A3,FY10_Holidays)"
to
adjesent cell (C3) in which i got an "ERROR!" msg in teh cell. What
is
wrong?




--
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: 558
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=194416

http://www.thecodecage.com/forumz

.