Prev: How do I insert a radio button using Excel 2007?
Next: Using Object & Worksheet names as variables
From: LABKHAND on 9 Apr 2010 15:01
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.
From: LABKHAND on 9 Apr 2010 15:03
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(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: Bob Phillips on 9 Apr 2010 15:28
I get 2 7 7 7 7 1 for January.

--

HTH

Bob

"LABKHAND" <LABKHAND(a)discussions.microsoft.com> wrote in message
news:8018A255-2DF9-4572-8AEA-CC0E90E59739(a)microsoft.com...
> 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(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: p45cal on 9 Apr 2010 17:05

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(TheYear, TheMonth, 1))
WeekNumofLastDayofTheMonth = Application.WorksheetFunction.WeekNum(DateSerial(TheYear, 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(StartDate, EndDate)
Else
WorkingDays = Application.WorksheetFunction.NetworkDays(StartDate, EndDate, Holidays)
End If
End If
LeaveNow:
End Function


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


--
p45cal

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

http://www.thecodecage.com/forumz

From: LABKHAND on 12 Apr 2010 09:44
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 labkhand(a)yahoo.com 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" <LABKHAND(a)discussions.microsoft.com> wrote in message
> news:8018A255-2DF9-4572-8AEA-CC0E90E59739(a)microsoft.com...
> > 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(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.
> >>
> >>
> >> .
> >>
>
>
> .
>
First  |  Prev  |  Next  |  Last
Pages: 1 2 3
Prev: How do I insert a radio button using Excel 2007?
Next: Using Object & Worksheet names as variables