How to calculate 6 working days between two dates
 

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

How to calculate 6 working days between two dates
 

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

How to calculate 6 working days between two dates
 

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

How to calculate 6 working days between two dates
 

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

How to calculate 6 working days between two dates
 

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