[SOLVED] Using VBA to create a Networkdays function

[Frank]

Hi is there a way to create a function that works like the worksheet function
"Networkdays" without using the worksheet I have been trying to do with VBA
without success.
Frank

[mangesh_yadav]

Function myWorkDay(myDate As Date, AddDays, LeaveDay)

If AddDays = 0 Then
myWorkDay = myDate
Exit Function
End If

For j = 1 To 7
If Choose(j, "Sunday", "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday") = LeaveDay Then
LD = j
End If
Next j


i = 1
Do While i <= AddDays
myWorkDay = myDate + i
If WorksheetFunction.Weekday(myWorkDay) = LD Then
AddDays = AddDays + 1
End If
i = i + 1
Loop

End Function

Usage:
=myWorkDay(A1,B1,C1)
Where A1 contains a date, B1 contains nos of days to add, C1 contains the day to leave out. E.g "Sunday"


Mangesh

[Frank]

Hi I was hopping to count number of days between two dates as with networkdays
thanks any way for your help
Frank
"mangesh_yadav" wrote:

>
> Function myWorkDay(myDate As Date, AddDays, LeaveDay)
>
> If AddDays = 0 Then
> myWorkDay = myDate
> Exit Function
> End If
>
> For j = 1 To 7
> If Choose(j, "Sunday", "Monday", "Tuesday", "Wednesday",
> "Thursday", "Friday", "Saturday") = LeaveDay Then
> LD = j
> End If
> Next j
>
>
> i = 1
> Do While i <= AddDays
> myWorkDay = myDate + i
> If WorksheetFunction.Weekday(myWorkDay) = LD Then
> AddDays = AddDays + 1
> End If
> i = i + 1
> Loop
>
> End Function
>
> Usage:
> =myWorkDay(A1,B1,C1)
> Where A1 contains a date, B1 contains nos of days to add, C1 contains
> the day to leave out. E.g "Sunday"
>
>
> Mangesh
>
>
> --
> mangesh_yadav
> ------------------------------------------------------------------------
> mangesh_yadav's Profile: http://www.excelforum.com/member.php...o&userid=10470
> View this thread: http://www.excelforum.com/showthread...hreadid=376705
>
>

[Mangesh Yadav]

Function myWorkDay2(myDate As Date, AddDays, LeaveDay, LeaveDay2)

If AddDays = 0 Then
myWorkDay2 = myDate
Exit Function
End If

For j = 1 To 7
If Choose(j, "Sunday", "Monday", "Tuesday", "Wednesday", "Thursday",
"Friday", "Saturday") = LeaveDay Then
LD = j
End If
If Choose(j, "Sunday", "Monday", "Tuesday", "Wednesday", "Thursday",
"Friday", "Saturday") = LeaveDay2 Then
LD2 = j
End If
Next j


i = 1
Do While i <= AddDays
myWorkDay2 = myDate + i
If WorksheetFunction.Weekday(myWorkDay2) = LD Or
WorksheetFunction.Weekday(myWorkDay2) = LD2 Then
AddDays = AddDays + 1
End If
i = i + 1
Loop

End Function


In this case specify both days e.g.
=myWorkDay2(A1,B1,"Saturday","Sunday")
A1 is date, B1 is nos of days


Mangesh



"Frank" <Frank@discussions.microsoft.com> wrote in message
news:6D697C3A-8C70-4F63-9F97-B3B0839CD0C2@microsoft.com...
> Hi I was hopping to count number of days between two dates as with
networkdays
> thanks any way for your help
> Frank
> "mangesh_yadav" wrote:
>
> >
> > Function myWorkDay(myDate As Date, AddDays, LeaveDay)
> >
> > If AddDays = 0 Then
> > myWorkDay = myDate
> > Exit Function
> > End If
> >
> > For j = 1 To 7
> > If Choose(j, "Sunday", "Monday", "Tuesday", "Wednesday",
> > "Thursday", "Friday", "Saturday") = LeaveDay Then
> > LD = j
> > End If
> > Next j
> >
> >
> > i = 1
> > Do While i <= AddDays
> > myWorkDay = myDate + i
> > If WorksheetFunction.Weekday(myWorkDay) = LD Then
> > AddDays = AddDays + 1
> > End If
> > i = i + 1
> > Loop
> >
> > End Function
> >
> > Usage:
> > =myWorkDay(A1,B1,C1)
> > Where A1 contains a date, B1 contains nos of days to add, C1 contains
> > the day to leave out. E.g "Sunday"
> >
> >
> > Mangesh
> >
> >
> > --
> > mangesh_yadav
> > ------------------------------------------------------------------------
> > mangesh_yadav's Profile:
http://www.excelforum.com/member.php...o&userid=10470
> > View this thread:
http://www.excelforum.com/showthread...hreadid=376705
> >
> >

[Dave Peterson]

Saved from a previous post:

If you want to use Networkdays in a worksheet, you'll need to load the analysis
toolpak. (Tools|addins). If it wasn't copied from the distribution disk (CD?),
you'll be prompted for the disk.

And if you want to use that function within your code, you could do any of
these:

Option Explicit
Sub testme()
With ActiveSheet
MsgBox networkdays(.Range("a1").Value, .Range("b1").Value)
MsgBox Application.Run("atpvbaen.xla!networkdays", _
.Range("a1").Value, .Range("b1").Value)
MsgBox .Evaluate("networkdays(a1,b1)")
End With
End Sub

The first two versions require that the analysis toolpak-VBA is loaded in excel
(tools|addins).

The first msgbox line requires a reference to the analysis toolpak for VBA
(tools|references|and check atpvbaen.xls). With that set, you can this function
just like it was built in.

The second needs to have the atpvbaen.xls addin loaded, but doesn't require the
reference in VBA.

The third doesn't need the atpvbaen.xls addin even loaded, but does require the
analysis toolpak itself.

Frank wrote:
>
> Hi is there a way to create a function that works like the worksheet function
> "Networkdays" without using the worksheet I have been trying to do with VBA
> without success.
> Frank

--

Dave Peterson

[Tom Ogilvy]

Frank,

The Late Frank Kabel wrote nested worksheet functions to replace many of the
functions in the analysis toolpak. These can be found at ****'s Daily Dose
of Excel (BLOG).

http://www.*****-blog.com/archives/2...-addin-part-1/
http://www.*****-blog.com/archives/2...-addin-part-2/
http://www.*****-blog.com/archives/2...-addin-part-3/

Part 2 covers that date/time functions and has nested function to do
networkdays.

--
Regards,
Tom Ogilvy



"Frank" <Frank@discussions.microsoft.com> wrote in message
news:C7134C6D-935D-44A0-910E-74601AC604C9@microsoft.com...
> Hi is there a way to create a function that works like the worksheet
function
> "Networkdays" without using the worksheet I have been trying to do with
VBA
> without success.
> Frank

[mangesh_yadav]

Hi Tom

Those are beautiful links. The thursday holiday problem which was discussed on some threads can be answered using these formulae.

one of the threads:
http://excelforum.com/showthread.php?t=373356

Mangesh