Yes, the date I want is 5th November 07. The 10 Mon and Thu would be 4th,
8th, 11th, 15th, 18th, 22nd, 25th, 29th, 1st Nov, 5th Nov
You could adapt my WrkDay UDF to do this. It is a UDF that mimics Excel's
Workday function, except that it allows you to specify which days of the week
are "weekend days".
To use the formula, you would enter it as:
=wrkday(A2,B2,,1,3,4,6,7)
A2 is your Start Date
B2 is the number of days you wish to add
Note the [,,]. The missing argument is an optional Holiday list, which makes
more sense in the context of the WrkDay function, but you could use it if there
were occasional Mons or Thursdays you did not want to count.
The 1,3,4,6,7 represent the days of the week Other than Mon or Thu.
(1 = Sunday, 3 = Tuesday, etc.)
To enter this UDF, <alt-F11> opens the VB Editor.
Ensure your project is highlighted in the Project Explorer window, then
Insert/Module and paste the code below into the window that opens.
The routines also include one that mimics the Excel NetWorkdays function, that
is required for the wrkday function.
Also, the Number of Days can be positive or negative.
Let me know if this does what you want.
====================================================
Function NWrkDays(StartDate As Date, EndDate As Date, _
Optional Holidays As Range = Nothing, _
Optional WeekendDay_1 As Integer = 0, _
Optional WeekendDay_2 As Integer = 0, _
Optional WeekendDay_3 As Integer = 0, _
Optional WeekendDay_4 As Integer = 0, _
Optional weekendday_5 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, _
WeekendDay_4, weekendday_5
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 = 0, _
Optional WeekendDay_2 As Integer = 0, _
Optional WeekendDay_3 As Integer = 0, _
Optional WeekendDay_4 As Integer = 0, _
Optional weekendday_5 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, WeekendDay_4, weekendday_5)
TempDate = TempDate + NumDays - Stp * (temp)
Loop
WrkDay = TempDate
End Function
==========================================
--ron
