[SOLVED] 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

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

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" <[email protected]> wrote in message
news:[email protected]...
> 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
> >
> >