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

On Sun, 6 Jun 2010 01:01:14 +0000, Mike H.. wrote:

>

>

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

Sorry Mike,

A1: 3 Jun 2010

Holidays

3 Jul 2010

4 Jul 2010

Your formula --> 4 Jul 2010

grrrr,

Thanks Ron, yes your correct it can cope with 1 holiday as the last day but not two as the last two days!!

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

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 address is 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!

Joining the fun, another array form

No not joining in the fun, "spoiling it" by solving the problem!!

Very nice indeed

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

Chapeau à lui !!!

Micky (Microsoft® MVP - Excel)