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

Hmmmmm,

I'm now going around in ever decreasing circles but given to OP isn't likely to have a holiday outside date+30 then this ARRAY formula works; I think!!!!

=(A1+((A1+30)-A1+SUM((holiday>=A1)*(holiday<=A1+(A1+((A1+30)-A1+SUM((holiday>=A1)*(holiday<=A1+30))))))))

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

If I haven't got it this time I'm giving up and I'm going to bed. Try this ARRAY formula, see below on how to enter an array formula

=A1+((A1+30)-A1+SUM((Holidays>=A1)*(Holidays<=A1+30)))

Where a1 = the start date and formula cell formatted as date and holidays = named range of holidays

This is an array formula which must be entered by pressing CTRL+Shift+Enter

and not just Enter. If you do it correctly then Excel will put curly brackets

around the formula {}. You can't type these yourself. If you edit the formula

you must enter it again with CTRL+Shift+Enter.

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

On Sat, 5 Jun 2010 20:46:09 +0000, Bernd Pl wrote:

>Hello Ron,

>

>Have you ever used iterations in a serious production environment? I do not mean a specialized math application which will only be run by experts.

>

>I found it far too dangerous for less experienced users because you will no longer see accidental circular references.

>

>That's why I put it on my Excel Dont's list: http://sulprobil.com/html/excel_don_ts.html , topic 5.

>

>Regards,

>

>Bernd

This is a matter of philosophy. Anytime you allow modifications to be

made to a worksheet, errors can certainly be produced.

If you have problems with iterations, where you or your users are

modifying your supplied spreadsheets, then I agree you would be

foolish to use it.

I've seen more errors with numbers entered as text, and with array

formulas not being properly entered, than I have with accidental

circular references that are not detected by other means.

Yup...I was missing a few somethings: Holidays prior to the start date being one of them. Thanks for checking.

Ron Coderre

Microsoft MVP - Excel (2006 - 2010)

P.S. If any post answers your question, please mark it as the Answer (so it won't keep showing as an open item.)

As far as I understood the question - the Holidays list should not / can not be limmited to within the range of those 30, or what days.

I compared Ron Rosenfelds last formula with Chip Pearsons suggestion [on his site]:

   http://www.cpearson.com/Excel/BetterWorkday.aspx

I checked:

Start date: 02/02/2009

Added days: 50

Holidays dates - in range: C18:C379 [coverring holidays for the years 2006-2012]

The range was build from fictitious Dates - some relevant for that period of time are along that long range.

Both solutions return the same answer [date]: 28th., Mar. 2009

Rons Codderre formula returns: 24th., Mar. 2009

Micky (Microsoft® MVP - Excel)