Prev: How do I insert a radio button using Excel 2007?
Next: Using Object & Worksheet names as variables
From: LABKHAND on 12 Apr 2010 10:06
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(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: p45cal on 12 Apr 2010 10:39

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.




L
A
B
K
H
A
N
D
;
6
9
6
8
7
2

W
r
o
t
e
:


>
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/showthread.php?t=194416

http://www.thecodecage.com/forumz

From: LABKHAND on 12 Apr 2010 12:03
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/showthread.php?t=194416
>
> http://www.thecodecage.com/forumz
>
> .
>
First  |  Prev  | 
Pages: 1 2 3
Prev: How do I insert a radio button using Excel 2007?
Next: Using Object & Worksheet names as variables