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

To all "Excel 2010" uers - there is a new function called: NETWORKDAYS.INTL

If my reply resolved your question/problem - please check it as 'Answered'.

If it was only for some help, please click the 'Vote as Helpful' button. Thanks.

Micky, Microsoft® Excel MVP [2009-2010] - ISRAEL

Hi Bernd,

If you were using a formula of that length in that many cells, and had the option of using VBA (many people in over-secured environments don't have that option), then VBA would surely be the way to go.

Cheers

Steve D.

"Bernd Pl" wrote in message news:cc6f5c51-b023-4acd-beb7-d12970c0103f...

Hello all,

Now, which approach should be applied/preferred for this problem in real life? If you need to use this in let's say about 30 cells of your spreadsheet?

Worksheet function?

Or VBA?

Regards,

Bernd

---

www.sulprobil.com

Hi Bob, can you explain why?

days*2+hols

works because the extension required would never exceed:

days*7*(days/7)+hols

Aaah, whoops, I wish I'd written that out earlier, it should of course have been:

days^2+hols

Now all bases are covered, unless you can explain why *hols+2 makes more sense?

=start_date+SIGN(days)*SMALL(IF((WEEKDAY(start_date+

SIGN(days)*(ROW(INDIRECT("1:"&ABS(days)^2+COUNT(

holidays)))))={1,2,3,4,5,6,7})*ISNA(MATCH(start_date+

SIGN(days)*(ROW(INDIRECT("1:"& ;ABS(days)^2+COUNT(

holidays)))),holidays,0)),ROW(INDIRECT("1:"&ABS(days)^

2+COUNT(holidays)))),ABS(days))

<rant mode>

BTW, why do the forums introduce spurious spaces in formulae?  I never saw that in newsgroups.

<\rant mode>

Cheers

Steve D.

"xld" wrote in message news:27192847-0838-44b9-b2d8-5e66f4294cf8...

I think COUNT(Holidays)+2 caters for most AFAICS

=start_date+SIGN(days)*SMALL(IF((WEEKDAY(start_date+SIGN(d ays)*(ROW(INDIRECT("1:"&ABS(days)*COUNT(Holidays)+2))))={5,6,7})*IS NA(MATCH(start_date+SIGN(days)*(ROW(INDIRECT("1:"&ABS(days)*COUNT(Hol idays)+2))),Holidays,0)),ROW(INDIRECT("1:"&ABS(days)*COUNT(Holidays)+ 2))),ABS(days))

--

HTH

Bob

<StunnRocker> wrote in message news:*** Email address is removed for privacy *** .com...

I WAS missing something.  To keep {1,2,3,4,5,6,7} uesful, we would need to multiply the number of days by weekly_holidays*(int(days/7)+1).

e.g.  to make all Fridays, Saturdays, and Sundays into holidays we would use {2,3,4,5} and ABS(days)*3*(INT(days/7)+1)+COUNT(holidays).

To keep things simple, ABS(days)*2+COUNT(holidays)  will cope with all circumstances.  So, if I'm right, the final formula would be:

=start_date+SIGN(days)*SMALL(IF((WEEKDAY(start_date+

SI GN(days)*(ROW(INDIRECT("1:"&ABS(days)*2+COUNT(

holidays)))))={1, 2,3,4,5,6,7})*ISNA(MATCH(start_date+

SIGN(days)*(ROW(INDIRECT("1:"& ;ABS(days)*2+COUNT(

holidays)))),holidays,0)),ROW(INDIRECT("1:"&AB S(days)*

2+COUNT(holidays)))),ABS(days))

Opinions?

Cheers

Steve D.

"StunnRocker" wrote in message news:f5adfcc0-632d-438 7-8adb-c53d7bc74cfe...

Excellent formula from Bob, but rather than ABS(days)*10,    ABS(days)+COUNT(holidays)  should cover it, unless I'm missing something.

"Mike H.." wrote in message news:cbc1420a-8267-4f5 9-934d-f259049b855a...

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 all,

Now, which approach should be applied/preferred for this problem in real life? If you need to use this in let's say about 30 cells of your spreadsheet?

Worksheet function?

Or VBA?

Regards,

Bernd

www.sulprobil.com

I think COUNT(Holidays)+2 caters for most AFAICS

=start_date+SIGN(days)*SMALL(IF((WEEKDAY(start_date+SIGN(d ays)*(ROW(INDIRECT("1:"&ABS(days)*COUNT(Holidays)+2))))={5,6,7})*IS NA(MATCH(start_date+SIGN(days)*(ROW(INDIRECT("1:"&ABS(days)*COUNT(Hol idays)+2))),Holidays,0)),ROW(INDIRECT("1:"&ABS(days)*COUNT(Holidays)+ 2))),ABS(days))

--

HTH

Bob

<StunnRocker> wrote in message news:*** Email address is removed for privacy *** .com...

I WAS missing something.  To keep {1,2,3,4,5,6,7} uesful, we would need to multiply the number of days by weekly_holidays*(int(days/7)+1).

e.g.  to make all Fridays, Saturdays, and Sundays into holidays we would use {2,3,4,5} and ABS(days)*3*(INT(days/7)+1)+COUNT(holidays).

To keep things simple, ABS(days)*2+COUNT(holidays)  will cope with all circumstances.  So, if I'm right, the final formula would be:

=start_date+SIGN(days)*SMALL(IF((WEEKDAY(start_date+

SI GN(days)*(ROW(INDIRECT("1:"&ABS(days)*2+COUNT(

holidays)))))={1, 2,3,4,5,6,7})*ISNA(MATCH(start_date+

SIGN(days)*(ROW(INDIRECT("1:"& ;ABS(days)*2+COUNT(

holidays)))),holidays,0)),ROW(INDIRECT("1:"&AB S(days)*

2+COUNT(holidays)))),ABS(days))

Opinions?

Cheers

Steve D.

"StunnRocker" wrote in message news:f5adfcc0-632d-438 7-8adb-c53d7bc74cfe...

Excellent formula from Bob, but rather than ABS(days)*10,    ABS(days)+COUNT(holidays)  should cover it, unless I'm missing something.

"Mike H.." wrote in message news:cbc1420a-8267-4f5 9-934d-f259049b855a...

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.