WORKDAY and holidays

[Jock]

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

 

[Stefi]

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

Regards,
Stefi

„Jock†ezt írta:

 

[Jock]

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

 

[Stefi]

If you have the start date in A1, number of days in B1 and holidays dates in
column C then
=A1+B1-(COUNTA(C:C)+1)
returns 14/01/09 if A1=01/01/09, B1=14 and there are no holidays in column C.
It returns 12/01/09 if you have two holidays in column C.

Regards,
Stefi

„Jock†ezt írta:

 

[Patrick Molloy]

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]

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,

 

[Rick Rothstein]

Will any of the dates for your holidays ever occur on a weekend? If **not**,
you could use NETWORKDAYS to calculate the number of holidays occurring
between your start and end dates by subtracting a NETWORKDAYS function call
with a reference to your holiday list from a NETWORKDAYS function call
without a reference to the holiday list... and subtract that number from the
difference between your start and end dates plus 1 (to include both the
start and end dates in your count). For example,

A1: Start Date
A2: End Date
H1: Start Holiday List
H9: End Holiday List

=A2-A1+1-(NETWORKDAYS(A1,A2)-NETWORKDAYS(A1,A2,H1:H2))

Remember, though, this assumes no holidays will take place on a weekend.

 

[Patrick Molloy]

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("D525")
With cell
.Offset(, 2) = WorkingDays(.Value, .Offset(, 1).Value, holidays)
End With
Next
End Sub

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

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,