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

Hello,

Sorry to spoil it: Here is an example for which Bob's worksheet function and Chip's UDF Workday2 do not work (I hope I got it right - please check):

http://dl.dropbox.com/u/6077606/Workdays2.xlsm

Regards,

Bernd

Joining the fun, another array formula

=start_date+SIGN(days)*SMALL(IF((WEEKDAY(start_date+SIGN(d ays)*(ROW(INDIRECT("1:"&ABS(days)*10))))={1,2,3,4,5,6,7})*

ISNA( MATCH(start_date+SIGN(days)*(ROW(INDIRECT("1:"&ABS(days)*10))),Holida ys,0)),ROW(INDIRECT("1:"&ABS(days)*10))),ABS(days))

days is the number of days to project forward.

--

HTH

Bob

<kaylith> wrote in message news:Email removed for privacy .com ...

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!

www.sulprobil.com

Hi,

I'm sure Bob's perfectly capable of speaking up for his own formula but here's my understanding

I still like Bob's formula a lot. It fails in your example becuase there are more than 9 holidays in the range, I believe it would cope with 10 if the named range 'Holidays' began in row 1.

Change the 10 in this -ABS(days)*10- to a larger number and it will cope with more holidays. I tested it up to 30 holidays and it works fine

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

Bernd,

Ditto for Chip's code, change this line

RunawayLoopControl = DaysRequired * 10

to

RunawayLoopControl = DaysRequired * 35

and it seems to work

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

Bernd,

 

Ditto for Chip's code, change this line

RunawayLoopControl = DaysRequired * 10

to

RunawayLoopControl = DaysRequired * 35

and it seems to work

---

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

Hello Mike,

You are right - Chip has already mentioned this restriction in his commentary.

I would add the number of Holidays to the RunawayLoopControl.

Then it works - in all cases, I guess.

Regards,

Bernd

www.sulprobil.com

Substituting range names in the formula I posted...apart from finding a date prior to the Start_Date,

...This array formula seems to be returning the correct answer each time:

=SMALL(IF(ISNA(MATCH(ROW(INDIRECT((Start_Date+1)&":"&(Start_Date+Days+COUNT(Holidays)))),Holidays,0)),ROW(INDIRECT((StartDate+1)&":"&(StartDate+Days+COUNT(Holidays)))),10^10),Days)

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