Calculating calendar days excluding holidays

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
 
D

Daniel.C

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
 
M

Mickey

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.
 
D

Daniel.C

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top