Calculate the number of days to exclude Sunday & Holidays
 

I need to know what the third business day from a Manually entered date in
Cell B37 to exclude Sundays and Holidays. So far I have:

=WORKDAY(B37,3,Holidays)

But I realized that I need it include Sat. and this formula won't do that.
Any suggestions?

THANKS!

--
-Liz

Calculate the number of days to exclude Sunday & Holidays
 

Hi,

Try this ARRAY formula. Holidays is a named range that you enter holiday
dates in. This adds the number of days in B1 but if you want you can change
every instance of B1 to a 3 (4 of them)

=B37+SMALL(IF(WEEKDAY(A1+ROW(INDIRECT("1:"&B1*10)) )<1,IF(ISNA(MATCH(A1+ROW(INDIRECT("1:"&B1*10)), Holidays,0)),ROW(INDIRECT("1:"&B1*10)))),B1)

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

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"LSG" wrote:

I need to know what the third business day from a Manually entered date in
Cell B37 to exclude Sundays and Holidays. So far I have:

=WORKDAY(B37,3,Holidays)

But I realized that I need it include Sat. and this formula won't do that.
Any suggestions?

THANKS!

--
-Liz

Calculate the number of days to exclude Sunday & Holidays
 

oops,

there's an error in the last formula, i wrote it for A1 then changed it to
B37 to meet your needs and forgot to change a reference

=B37+SMALL(IF(WEEKDAY(B37+ROW(INDIRECT("1:"&B1*10) ))<1,IF(ISNA(MATCH(B37+ROW(INDIRECT("1:"&B1*10)), Holidays,0)),ROW(INDIRECT("1:"&B1*10)))),B1)
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Mike H" wrote:

Hi,

Try this ARRAY formula. Holidays is a named range that you enter holiday
dates in. This adds the number of days in B1 but if you want you can change
every instance of B1 to a 3 (4 of them)

=B37+SMALL(IF(WEEKDAY(A1+ROW(INDIRECT("1:"&B1*10)) )<1,IF(ISNA(MATCH(A1+ROW(INDIRECT("1:"&B1*10)), Holidays,0)),ROW(INDIRECT("1:"&B1*10)))),B1)

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

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"LSG" wrote:

I need to know what the third business day from a Manually entered date in
Cell B37 to exclude Sundays and Holidays. So far I have:

=WORKDAY(B37,3,Holidays)

But I realized that I need it include Sat. and this formula won't do that.
Any suggestions?

THANKS!

--
-Liz

Calculate the number of days to exclude Sunday & Holidays
 

thanks for the formula, but I'm getting an error message. What is 'B1'
suppose to be?
--
-Liz


"Mike H" wrote:

oops,

there's an error in the last formula, i wrote it for A1 then changed it to
B37 to meet your needs and forgot to change a reference

=B37+SMALL(IF(WEEKDAY(B37+ROW(INDIRECT("1:"&B1*10) ))<1,IF(ISNA(MATCH(B37+ROW(INDIRECT("1:"&B1*10)), Holidays,0)),ROW(INDIRECT("1:"&B1*10)))),B1)
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Mike H" wrote:

Hi,

Try this ARRAY formula. Holidays is a named range that you enter holiday
dates in. This adds the number of days in B1 but if you want you can change
every instance of B1 to a 3 (4 of them)

=B37+SMALL(IF(WEEKDAY(A1+ROW(INDIRECT("1:"&B1*10)) )<1,IF(ISNA(MATCH(A1+ROW(INDIRECT("1:"&B1*10)), Holidays,0)),ROW(INDIRECT("1:"&B1*10)))),B1)

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

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"LSG" wrote:

I need to know what the third business day from a Manually entered date in
Cell B37 to exclude Sundays and Holidays. So far I have:

=WORKDAY(B37,3,Holidays)

But I realized that I need it include Sat. and this formula won't do that.
Any suggestions?

THANKS!

--
-Liz

Calculate the number of days to exclude Sunday & Holidays
 

thanks for the formula, but I'm getting an error message. What is 'B1'
suppose to be?


From My first post

Try this ARRAY formula. Holidays is a named range that you enter holiday
dates in. This adds the number of days in B1 but if you want you can change
every instance of B1 to a 3 (4 of them)

--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"LSG" wrote:

thanks for the formula, but I'm getting an error message. What is 'B1'
suppose to be?
--
-Liz


"Mike H" wrote:

oops,

there's an error in the last formula, i wrote it for A1 then changed it to
B37 to meet your needs and forgot to change a reference

=B37+SMALL(IF(WEEKDAY(B37+ROW(INDIRECT("1:"&B1*10) ))<1,IF(ISNA(MATCH(B37+ROW(INDIRECT("1:"&B1*10)), Holidays,0)),ROW(INDIRECT("1:"&B1*10)))),B1)
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Mike H" wrote:

Hi,

Try this ARRAY formula. Holidays is a named range that you enter holiday
dates in. This adds the number of days in B1 but if you want you can change
every instance of B1 to a 3 (4 of them)

=B37+SMALL(IF(WEEKDAY(A1+ROW(INDIRECT("1:"&B1*10)) )<1,IF(ISNA(MATCH(A1+ROW(INDIRECT("1:"&B1*10)), Holidays,0)),ROW(INDIRECT("1:"&B1*10)))),B1)

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

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"LSG" wrote:

I need to know what the third business day from a Manually entered date in
Cell B37 to exclude Sundays and Holidays. So far I have:

=WORKDAY(B37,3,Holidays)

But I realized that I need it include Sat. and this formula won't do that.
Any suggestions?

THANKS!

--
-Liz

Calculate the number of days to exclude Sunday & Holidays
 

Oops, sorry I had misread that. But it works now! thanks!
--
-Liz


"Mike H" wrote:

thanks for the formula, but I'm getting an error message. What is 'B1'
suppose to be?


From My first post

Try this ARRAY formula. Holidays is a named range that you enter holiday
dates in. This adds the number of days in B1 but if you want you can change
every instance of B1 to a 3 (4 of them)

--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"LSG" wrote:

thanks for the formula, but I'm getting an error message. What is 'B1'
suppose to be?
--
-Liz


"Mike H" wrote:

oops,

there's an error in the last formula, i wrote it for A1 then changed it to
B37 to meet your needs and forgot to change a reference

=B37+SMALL(IF(WEEKDAY(B37+ROW(INDIRECT("1:"&B1*10) ))<1,IF(ISNA(MATCH(B37+ROW(INDIRECT("1:"&B1*10)), Holidays,0)),ROW(INDIRECT("1:"&B1*10)))),B1)
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Mike H" wrote:

Hi,

Try this ARRAY formula. Holidays is a named range that you enter holiday
dates in. This adds the number of days in B1 but if you want you can change
every instance of B1 to a 3 (4 of them)

=B37+SMALL(IF(WEEKDAY(A1+ROW(INDIRECT("1:"&B1*10)) )<1,IF(ISNA(MATCH(A1+ROW(INDIRECT("1:"&B1*10)), Holidays,0)),ROW(INDIRECT("1:"&B1*10)))),B1)

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

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"LSG" wrote:

I need to know what the third business day from a Manually entered date in
Cell B37 to exclude Sundays and Holidays. So far I have:

=WORKDAY(B37,3,Holidays)

But I realized that I need it include Sat. and this formula won't do that.
Any suggestions?

THANKS!

--
-Liz

Calculate the number of days to exclude Sunday & Holidays
 

Here's another one...

Create these defined names...

InsertNameDefine
Name: Array
Refers to: ={1;2;3;4;5;6;7;8;9;10}

Name: Days
Refers to: ={2,3,4,5,6,7}
OK out

A1 = some date
B1 = the number of workdays* = 3
Holidays = range of dates to be excluded

Then, array entered** :

=A1+1*SMALL(IF(WEEKDAY(A1+1*Array)=Days*ISNA(MATCH (A1+1*Array,Holidays,0)),Array),B1)

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

* this formula is specifically written to calculate *future dates* so the
number of workdays must be a positive number.

--
Biff
Microsoft Excel MVP


"LSG" wrote in message
...
I need to know what the third business day from a Manually entered date in
Cell B37 to exclude Sundays and Holidays. So far I have:

=WORKDAY(B37,3,Holidays)

But I realized that I need it include Sat. and this formula won't do that.
Any suggestions?

THANKS!

--
-Liz