Calculating calendar days excluding holidays

  • Thread starter Thread starter Mickey
  • Start date Start date
M

Mickey

Need to determine a date by selecting a start date then add x days and
exclude holdays. (I know the formula for workdays excluding holidays) Need
to include weekend and exclude holidays
 
Suppose start date in A1, end date in B1, holidays in column C :
=B1-A1-SUMPRODUCT((C1:C20>=A1)*(C1:C20<=B1))
Do you need VBA syntax ?
Daniel
 
Thanks Daniel - below may be a better explanation of what I'm trying to
calculate.

My start date is 12/1/08, I need to add 60 days excluding holidays. I have
the holidays defline in a lookup table. The formula =workdays(a1-a2,holdays)
returns a date that excludes weekends and holidays. I need to include
weekends but exclude holidays.
 
Sorry to have overlooked your problem.
Try :

Sub test()
'A1 = start date
'A2 = result
'B1 = days to add
'Column C = holidays
Dim Ctr As Integer, i As Integer
Do Until Ctr = [B1]
i = i + 1
If Not IsNumeric(Application.Match(([A1] + i) * 1, [C:C], 0))
Then
Ctr = Ctr + 1
End If
Loop
[A2] = [A1] + i
End Sub

Daniel
 
Back
Top