Niek Otten
12/14/2006 11:57:00 PM
And, in case you want to have absolute control over the definition of workdays, this UDF
--
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
' ==========================
"daddylonglegs" <daddylonglegNOSPAM@hotmail.com> wrote in message news:774F0318-A6E0-4375-B217-D241B3F83E3C@microsoft.com...
| You'd have to make a list of holiday dates, assume these are in C1:C10 and
| you want to add a number of days contained in B1 to a date in A1 you could
| use this formula
|
| =MIN(IF(ISNA(MATCH(A1+B1+{0,1,2,3,4,5},C1:C10,0)),A1+B1+{0,1,2,3,4,5}))
|
| This assumes you never have more than 5 bank holidays on consecutive days
|
| "TheRook" wrote:
|
| > Thanks for that, the only problem is that it seems to be assuming that we do
| > not work weekends but that is not the case.
| >
| > Is it possible for it to assume everyday is a working day bar the ones
| > stated as holidays?
| >
| > Regards
| >
| > "Niek Otten" wrote:
| >
| > > From the same family as NETWORKDAYS():
| > > WORKDAY()
| > > You may have to format the result as Date
| > >
| > > --
| > > Kind regards,
| > >
| > > Niek Otten
| > > Microsoft MVP - Excel
| > >
| > >
| > > "TheRook" <TheRook@discussions.microsoft.com> wrote in message news:3185C7E4-1CC6-4558-8FBC-E5DC3F489538@microsoft.com...
| > > |I am wanting to calculate an end date from a start date say 1/12 + 7 days =
| > > | 8/12 but if 2/12 was classed as an holiday the the end date would be 9/12
| > > |
| > > | A have previously used networkdays using holidays as non working days but
| > > | obviously this is to calculate from a start date to an end date, and not to
| > > | prodicte an end date.
| > > |
| > > | anyone any ideas?
| > >
| > >
| > >