Can I calculate 30 calendar days from a given date, excluding holidays that I specify?

On Fri, 4 Jun 2010 20:34:51 +0000, kaylith

<*** Email address is removed for privacy ***>

wrote:

>

>

>I'm looking for something similar to the WORKDAY function, except I want it to return dates that include weekends (but not holidays that I specify).

=StartDate+DaysToAdd+SUMPRODUCT(COUNTIF(Holidays,

ROW(INDIRECT(StartDate&":"&INDIRECT(CELL("address"))))))

OOPS,

That formula should be:

B1:

=StartDate+DaysToAdd+SUMPRODUCT(

COUNTIF(Holidays,ROW(INDIRECT(StartDate&":"&B1))))

Note I removed the cell("reference") construct and replaced it with the actual cell containing the formula.  The first version was for some testing.

On Fri, 4 Jun 2010 20:34:51 +0000, kaylith

<*** Email address is removed for privacy ***>

wrote:

>

>

>I'm looking for something similar to the WORKDAY function, except I want it to return dates that include weekends (but not holidays that I specify).

>

>

>

>Thanks for your advice!

If you enable circular references (or iterations) which is under Excel

Options in 2007+ , and under Tools/Options in earlier versions of

Excel, AND set the number of allowed iterations to be any value at

least as great as the number of Holidays in your list (it can be much

larger), then you can use this formula:

=StartDate+DaysToAdd+SUMPRODUCT(COUNTIF(Holidays,

ROW(INDIRECT(StartDate&":"&INDIRECT(CELL("address"))))))

Bernd,

Thanks for that, teach me to test properly before posting.

If this post answers your question, please mark it as the Answer.

Hi,

A1 = start date and holidays is a named range of dates to exclude

=WORKDAY(A1,30,Holidays)-SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&WORKDAY(A1,30,Holidays))),2)>5))

---

If this post answers your question, please mark it as the Answer.

Hello Mike,

That does not work: Try A1 = 5-June-2010. Holidays: 30-June-2010, 6-July-2010, 7-July-2010. Your formula returns 7-July-2010, a holiday. Correct is 8-July-2010.

I suggest to use:

=ThirtyDaysAfter(A1,Holidays)

Function ThirtyDaysAfter(dt As Date, vHolidays As Variant) As Date

Dim vwh(1 To 7, 1 To 2) As Variant

vwh(1, 1) = 0: vwh(1, 2) = 1

vwh(2, 1) = 0: vwh(2, 2) = 1

vwh(3, 1) = 0: vwh(3, 2) = 1

vwh(4, 1) = 0: vwh(4, 2) = 1

vwh(5, 1) = 0: vwh(5, 2) = 1

vwh(6, 1) = 0: vwh(6, 2) = 1

vwh(7, 1) = 0: vwh(7, 2) = 1

ThirtyDaysAfter = Int(add_hours(dt, 30.0001, vwh, vHolidays))

End Function

The function add_hours:

http://sulprobil.com/html/count\_hours.html

Regards,

Bernd

www.sulprobil.com