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
|