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

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 InsertModule. 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(..)
================================================



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

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.

Would the NETWORKDAYS function work as you want if you subtracted one from
all dates passed into it?

Rick