About Us

Addatone

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.

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
difference between two dates & time by excluding holidays & weeken	Rohit	Excel Programming	1	February 20th 09 12:02 PM
Calculates Dates Based on 7 day week excluding Holidays	Daviv	Excel Discussion (Misc queries)	1	January 17th 07 07:02 PM
fill a series of dates excluding holidays	gsh20	Excel Worksheet Functions	1	August 25th 05 12:33 AM
How do you count work days excluding weekends and holidays?	Hausma	Excel Discussion (Misc queries)	2	April 8th 05 07:39 PM
formula to calculate # of days between dates, excluding holidays	abs2299	Excel Discussion (Misc queries)	8	March 3rd 05 02:21 AM

Menu

About Us