I have two dates from 9 Nov 2007 to 3 Dec 2007.
I want to calculate workdays for this mentained range & getting result as 17
Days
by putting the formula as networkdays. Problem is that I want to consider 6
working days instead of 5 working days. How to get results??

Try:

Sub Test()
MsgBox GetWorkdays("9 Nov 2007", "3 Dec 2007")
End Sub

Function GetWorkdays(FirstDate As Date, LastDate As Date) As Integer
Dim i As Integer, ii As Integer
ii = 0
For i = 0 To (LastDate - FirstDate)
If Weekday(FirstDate + i) <> 1 Then ii = ii + 1
Next
GetWorkdays = ii
End Function

Regards,
Greg

Thanks, it works...

But the details I actualy need to fill in Excel worksheet is as follows:

Start Date: 9 Nov 2007
End Date : 3 Dec 2007
1) Sundays on : dd-mm-yy , dd-mm-yy,........
2) Holidays on : ........
3) Total Working days : ?? (by considering Monday to saturday as working Days)
I am to too exposed to programing, so please provide me the simplest
way to get the results that I need.
Thanks in advance for your help.

*Nilay*





"Greg Wilson" wrote:

> Try:
>
> Sub Test()
> MsgBox GetWorkdays("9 Nov 2007", "3 Dec 2007")
> End Sub
>
> Function GetWorkdays(FirstDate As Date, LastDate As Date) As Integer
> Dim i As Integer, ii As Integer
> ii = 0
> For i = 0 To (LastDate - FirstDate)
> If Weekday(FirstDate + i) <> 1 Then ii = ii + 1
> Next
> GetWorkdays = ii
> End Function
>
> Regards,
> Greg

You might also try in the worksheet function ng for another option.

Function example:-
1. Put the 1st date in cell A1
2. Put the last date in cell A2
3. Put in cells A3 to A12 the holidays
4. Enter this in cell C1:
=GetWorkdays(A1, A2, A3:A12)

Function GetWorkdays(FirstDate As Date, LastDate As Date, _
Optional Hols As Variant) As Integer
Dim i As Integer, ii As Integer, wkdys As Integer
Dim dy As Date
Dim f As Boolean

wkdys = 0
For i = 0 To (LastDate - FirstDate)
dy = CDate(FirstDate + i)
If Weekday(dy) <> 1 Then
f = False
If Not IsMissing(Hols) Then
For ii = 1 To Hols.Count
If Len(Hols(ii)) = 0 Then Exit For
If CDate(Hols(ii)) = dy Then
f = True
Exit For
End If
Next
End If
If Not f Then wkdys = wkdys + 1
End If
Next
GetWorkdays = wkdys
End Function

Greg

Dear Greg,
Thanks. This function is working absolutely fine.

With Regards,
Nilay


"Greg Wilson" wrote:

> You might also try in the worksheet function ng for another option.
>
> Function example:-
> 1. Put the 1st date in cell A1
> 2. Put the last date in cell A2
> 3. Put in cells A3 to A12 the holidays
> 4. Enter this in cell C1:
> =GetWorkdays(A1, A2, A3:A12)
>
> Function GetWorkdays(FirstDate As Date, LastDate As Date, _
> Optional Hols As Variant) As Integer
> Dim i As Integer, ii As Integer, wkdys As Integer
> Dim dy As Date
> Dim f As Boolean
>
> wkdys = 0
> For i = 0 To (LastDate - FirstDate)
> dy = CDate(FirstDate + i)
> If Weekday(dy) <> 1 Then
> f = False
> If Not IsMissing(Hols) Then
> For ii = 1 To Hols.Count
> If Len(Hols(ii)) = 0 Then Exit For
> If CDate(Hols(ii)) = dy Then
> f = True
> Exit For
> End If
> Next
> End If
> If Not f Then wkdys = wkdys + 1
> End If
> Next
> GetWorkdays = wkdys
> End Function
>
> Greg