Hello Ron,
Thanks for your prompt reply and assistance.
I apologize for the miscommunication in regards to the below quote:
Ithink the formula needs to be adjusted to only exclude holidays and the
Start Date.[/i][/color]
I meant to say, "The formula needs to be adjusted to only exclude holidays and the Start Date and not to exclude the End Date as I originally wrote".
The new formula you sent works perfectly and I'm no longer getting the #REF! error when there is one or less WorkDays left in the month. Yay!
In regards to your question, a Start Date can never be on a Holiday or weekend. It always has to be a Business Day.
Thanks once again for your help. You totally saved the day.
Later,
Addatone
Quote:
Originally Posted by Ron Rosenfeld[_2_]
On Wed, 24 Jul 2013 16:27:53 +0100, Addatone wrote:
I tried removing the End Date from the formula but get the #VALUE!.
Ithink the formula needs to be adjusted to only exclude holidays and the
Start Date.
Please provide an example where it is excluding other dates. It should not be.
My concern then is when the Start Date and End Date are the
same, e.g 07/31/2013. What happens?
In that case, or any case (such as your August example), where there is one or less WorkDays, an error will result.
A quick fix is to test for that condition:
=IF(NETWORKDAYS(StartDate,EndDate,Holidays)<=1,0,S UMPRODUCT(
--(WEEKDAY(WORKDAY(StartDate,ROW(INDIRECT("1:"&-1+
NETWORKDAYS(StartDate,EndDate,Holidays))),Holidays ))=DOW)))
However, there is another potential problem -- can StartDate ever be on a weekend or holiday? And, if so, how should that be handled? (If not, I still have to make some changes, but I don't have time right now).
Later.
|