On Fri, 27 May 2005 15:26:36 +0530, pankaj_zen
wrote:


Hi all,
i have a unique problem.
here we have thursdays as week ends ( sat & sundays are working days).
Now what i want to do is in anexcel sheet if i enter say for example
enter 24 may which is tuesday in any of the cells & digit 5 in another
cell, when i add these two i should get 30 may i.e it should jump
thursday my weekly off.
the number can be any thing from1 to 70.

Can any one please help me in this regard?


Here is a UDF that, with the correct arguments, will treat Thursday as your
weekend.

To enter it, <alt<F11 opens the VB Editor. Ensure your project is
highlighted in the project explorer window, then Insert/Module and paste the
below code into the window that opens.

To use it, enter the formula in the form of:

=Wrkday(A1, A2,,5)

Where A1 contains the date and A2 contains the number of days to add.
The blank ( ,, ) is an optional list of holidays which can be another range.
'5' represents Thursday as you can see from the comments in the udf.

====================================
Function WrkDay(StartDate As Date, ByVal NumDays As Long, _
Optional Holidays As Range = Nothing, _
Optional WeekendDay_1 As Integer = 0, _
Optional WeekendDay_2 As Integer = 0, _
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
====================================


--ron