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

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

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.

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.

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

Chapeau à lui !!!

Micky (Microsoft® MVP - Excel)