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

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

I agree - the larger number should just be the number of holidays, I guess.

Regards,

Bernd

www.sulprobil.com

Here's another one...

Similar to Ron's.

Works as long as the start date is not a holiday and the number of days is >0. Only works for a future date.

B1 = start date

B2 = number of days

Holidays is a named range of cells that contain dates to be excluded

Array entered** :

=SMALL(IF(ISNA(MATCH(ROW(INDIRECT("1:"&B2*COUNT(Holidays)))+B1,Holidays,0)),ROW(INDIRECT("1:"&B2*COUNT(Holidays)))),B2)+B1

>teach me to test properly before posting.

Here's how I tested this formula...

A12 formula: =B1

A13 formula: =A12+1 copy this down a few hundred rows (I copied to 600 rows)

B13 formula: =IF(COUNTIF(Holidays,A13),"",MAX(B$12:B12)+1) copy down as far as the test formulas in column A.

Then, use this formula to get the correct result for the test confirmation:

=INDEX(A12:A612,MATCH(B2,B12:B612,0))

I have buttons on a toolbar (Excel 2002) that generate random dates and random numbers. So changing the holiday dates and the number of days for testing is really easy.

** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER.

--

Biff

Microsoft Excel MVP

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

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+

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

Opinions?

Cheers

Steve D.

"StunnRocker" wrote in message news:f5adfcc0-632d-4387-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-4f59-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.

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.