Calculate the number of days to exclude Sunday & Holidays

  • Thread starter Thread starter LSG
  • Start date Start date
L

LSG

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!
 
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.
 
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.
 
thanks for the formula, but I'm getting an error message. What is 'B1'
suppose to be?
Click to expand...


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.
 
Here's another one...

Create these defined names...

Insert>Name>Define
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.
 
Back
Top