Hi Narenda,
Try this UDF.
Instructions for implementing a UDF at the bottom of this post.
--
Kind regards,
Niek Otten
Microsoft MVP - Excel
' ===========================
' Ron Rosenfeld
' Copied form Google's Newsgroup Archives April 27, 2006
Function NWrkDays(StartDate As Date, EndDate As Date, _
Optional Holidays As Range = Nothing, _
Optional WeekendDay_1 As Integer = 1, _
Optional WeekendDay_2 As Integer = 7, _
Optional WeekendDay_3 As Integer = 0) As Long
' Sunday = 1; Monday = 2; ... Saturday = 7
'credits to Myrna
Dim i As Long
Dim Count As Long
Dim H As Variant
Dim w As Long
Dim SD As Date, ED As Date
Dim DoHolidays As Boolean
Dim NegCount As Boolean
DoHolidays = Not (Holidays Is Nothing)
SD = StartDate: ED = EndDate
If SD > ED Then
SD = EndDate: ED = StartDate
NegCount = True
End If
w = Weekday(SD - 1)
For i = SD To ED
Count = Count + 1
w = (w Mod 7) + 1
Select Case w
Case WeekendDay_1, WeekendDay_2, WeekendDay_3
Count = Count - 1
Case Else
If DoHolidays Then
If IsNumeric(Application.Match(i, Holidays, 0)) Then _
Count = Count - 1
End If
End Select
Next i
If NegCount = True Then Count = -Count
NWrkDays = Count
End Function
Function WrkDay(StartDate As Date, ByVal NumDays As Long, _
Optional Holidays As Range = Nothing, _
Optional WeekendDay_1 As Integer = 1, _
Optional WeekendDay_2 As Integer = 7, _
Optional WeekendDay_3 As Integer = 0) As Date
' Sunday = 1; Monday = 2; ... Saturday = 7
Dim i As Long
Dim TempDate As Date
Dim Stp As Integer
Dim NonWrkDays As Long
Dim temp As Long, SD As Date, ED As Date
Stp = Sgn(NumDays)
'Add NumDays
TempDate = StartDate + NumDays
'Add Non-Workdays
Do While Abs(NumDays) <> temp
SD = Application.WorksheetFunction.Min(StartDate + Stp, TempDate)
ED = Application.WorksheetFunction.Max(StartDate + Stp, TempDate)
temp = NWrkDays(SD, ED, Holidays, WeekendDay_1, WeekendDay_2, WeekendDay_3)
TempDate = TempDate + NumDays - Stp * (temp)
Loop
WrkDay = TempDate
End Function
' ==========================
================================================
Pasting a User Defined Function (UDF)
Niek Otten, March 31, 2006
If you find a VBA function on the Internet or somebody mails you one, and you don't know how to implement it, follow
these steps:
Select all the text of the function.
CTRL+C (that is, press and hold down the CTRL key, press C, release both). This a shortcut for Copy.
Go to Excel. Press ALT+F11 (same method: press and hold the ALT key, press the F11 key and release both). You are now
in the Visual Basic Editor (VBE).
From the menu bar, choose Insert>Module. There should now be a blank module sheet in front of you. Click in it and
then press CTRL+V (same method.). This a shortcut for Paste. You should now see the text of the function in the Module.
Press ALT+F11 again to return to your Excel worksheet.
You should now be able to use the function as if it were a built-in function of Excel, like =SUM(..)
================================================
| Hi...
|
| Ima working in an Indain company. In our company we have week offs of
| Sunday and monday. The networkdays( ) function counts only saturday and
| sundays as week offs. But I need to change this default days to my
| customized days.
|
| Does any one suggest me how to change the week-offs in Excel.
|
| Expecting good suggestion....
|
| Narendra
|
|
