Using VBA to create a Networkdays function
 

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

Using VBA to create a Networkdays function
 

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

Using VBA to create a Networkdays function
 

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

Using VBA to create a Networkdays function
 

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" wrote in message
...
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

Using VBA to create a Networkdays function
 

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

Using VBA to create a Networkdays function
 

Frank,

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

http://www.dicks-blog.com/archives/2...-addin-part-1/
http://www.dicks-blog.com/archives/2...-addin-part-2/
http://www.dicks-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" wrote in message
...
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

Using VBA to create a Networkdays function
 

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


--
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