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

A1: Start date

A2: number of days to be add

A3:A4 holidays

=A1+A2+SUMPRODUCT(--ISNUMBER(MATCH(ROW(INDIRECT(A1&":"&A1+A2)),A3:A4,0)))

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

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

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

www.sulprobil.com

Mr. Pearson,

I wonder you didn't remember the hereunder solution of yours(!):

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

Micky (Microsoft® MVP - Excel)

Here is a formula approach which is rather complex, but seems to work.  1. Assume your start date is in A1, and your list of holidays in C1:C10.  Also assume that there can be a max of 3 holidays in any 35 consecutive days.  then in D1 enter the following array** formula:

=IF(ISERR(SMALL(IF((C$1:C$10>=A$1)*(C$1:C$10<=A$1+40),C$1:C$10,""),ROW(A1))),"",SMALL(IF((C$1:C$10>=A$1)*(C$1:C$10<=A$1+40),C$1:C$10,""),ROW(A1)))

and copy it down 3 cells.  (the holidays we will look at are in column D.)  ** Array formula means you enter it by pressing Shift+Ctrl+Enter rather than Enter.

In cell A2 we calculate the end date with the following array** formula:

=A1+SMALL(IF((((A1+ROW(1:35))<>D$1)+((A1+ROW(1:35))<>D$2)+((A1+ROW(1:35))<>D$3)=3)*ROW(1:35)>0,(((A1+ROW(1:35))<>D$1)+((A1+ROW(1:35))<>D$2)+((A1+ROW(1:35))<>D$3)=3)*ROW(1:35),""),31)-1

If this answer solves your problem, please check Mark as Answered. If this answer helps, please click the Vote as Helpful button. Cheers, Shane Devenshire

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.