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
|