I have a sheet called Holidays, and a table named BankHolidays
sheet1 has my dates in D5... and the days in E5.... the answer will be put
in D5...


Option Explicit
Sub setstopdate()
Dim holidays As Range
Dim cell As Range
Set holidays = Worksheets("Holidays").Range("BankHolidays")
For Each cell In Range("D5:D25")
With cell
.Offset(, 2) = WorkingDays(.Value, .Offset(, 1).Value, holidays)
End With
Next
End Sub

alternative
in D5: =WorkingDays(D5,E5,BankHolidays)

"Jock" wrote in message
...
This looks promising!
I have more than one column which would need to use this UDF so can I
incorporate those or would this be specific to one range?
Can you help with the input lines - ie if you give an example, I can adapt
to suit?

Thanks,

--
Traa Dy Liooar

Jock


"Patrick Molloy" wrote:

you need a UDF to do this

Option Explicit
Function WorkingDays(startdate As Date, nDays As Long, Optional holidays
As
Range) As Date
' INPUT : start date
' : number of days (n)
' : range of holidays (optional)
'OUTPUT: Date n days from Start Date adjusting fir holidays
Dim i As Long
Dim thisDate As Date
thisDate = startdate
i = 1
Do Until i = nDays
If Not isHoliday(thisDate, holidays) Then
i = i + 1
End If
thisDate = thisDate + 1
Loop
WorkingDays = thisDate
End Function
Function isHoliday(sDate As Date, source As Range) As Boolean
On Error Resume Next
isHoliday = WorksheetFunction.Match(sDate * 1, source, False) < 0
End Function





"Jock" wrote in message
...
Exactly! I want it to include weekends, not ignore them.

I.E. 14 days from 01/01/09 will be 14/01/09 not 21/01/09 as it is using
WORKDAY
--
Traa Dy Liooar

Jock


"Stefi" wrote:

You are wrong! WORKDAY does take care both of weekends and holidays!
Check it
again!

Regards,
Stefi

€žJock€� ezt Ã*rta:

Hi,
I need to have a date formula which will not ignore weekends and
will
take
into account a list of holiday dates.
WORKDAY takes care of the holiday list but ignores weekends.
Is there another function that will include weekends AND holidays?
p.s. any weekends within the holiday dates can be ignored.

Thanks
--
Traa Dy Liooar

Jock