From the same family as NETWORKDAYS():
WORKDAY()
You may have to format the result as Date

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


"TheRook" wrote in message ...
|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?

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

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