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

On Sun, 6 Jun 2010 02:04:59 +0000, Mike H.. wrote:

>

>

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

I think your restriction of not allowing a holiday in the holiday list

outside date+30 is unrealistic. I think it more likely the OP might

have a list of holiday dates for the year.

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