Hello,

I need some help. I need to count the remaining weekdays (eg. Mondays) in a date range. I also need to exclude holidays and the date range in my count.


A1 Start Date: 07/01/2013
B2 End Date: 07/31/2013

A4 Holidays
A5 01/01/2013
A6 05/27/2013
A7 07/04/2013

A9 Number of Remaining Mondays
A10 5
A11 Number of Remaining Thursdays (excluding holiday)
A12 3

A10 =INT((WEEKDAY(A2-2)-A2+B2)/7)-SUMPRODUCT(--(WEEKDAY(A5:A7)=2),--(A5:A7=A2),--(A5:A7<=B2))

A12 =INT((WEEKDAY(A2-5)-A2+B2)/7)-SUMPRODUCT(--(WEEKDAY(A5:A7)=5),--(A5:A7=A2),--(A5:A7<=B2))

I need A10 to be 4 to exclude the start date. Whenever I change the start date, the count for the remaining weekdays must excludes that start date Eg. On Tuesday, 07/30/2013, there should be 0 count for the remaining Tuesdays in the date range btw 07/30/2013 and 07/31/2013. Likewise, for Wednesday 07/31/2013, there is a 0 count for the remaining Wednesdays in the month of July.

Please help.

Thanks Addatone

On Tue, 23 Jul 2013 22:32:50 +0100, Addatone wrote:


Hello,

I need some help. I need to count the remaining weekdays (eg. Mondays)
in a date range. I also need to exclude holidays and the date range in
my count.


A1 Start Date: 07/01/2013
B2 End Date: 07/31/2013

A4 Holidays
A5 01/01/2013
A6 05/27/2013
A7 07/04/2013

A9 Number of Remaining Mondays
A10 5
A11 Number of Remaining Thursdays (excluding holiday)
A12 3

A10
=INT((WEEKDAY(A2-2)-A2+B2)/7)-SUMPRODUCT(--(WEEKDAY(A5:A7)=2),--(A5:A7=A2),--(A5:A7<=B2))

A12
=INT((WEEKDAY(A2-5)-A2+B2)/7)-SUMPRODUCT(--(WEEKDAY(A5:A7)=5),--(A5:A7=A2),--(A5:A7<=B2))

I need A10 to be 4 to exclude the start date. Whenever I change the
start date, the count for the remaining weekdays must excludes that
start date Eg. On Tuesday, 07/30/2013, there should be 0 count for the
remaining Tuesdays in the date range btw 07/30/2013 and 07/31/2013.
Likewise, for Wednesday 07/31/2013, there is a 0 count for the remaining
Wednesdays in the month of July.

Please help.

Thanks Addatone


Given a Start Date and End Date, the following formula will return the number of any particular weekday, not counting the Start Date:

=SUMPRODUCT(--(WEEKDAY(WORKDAY(StartDate,ROW(
INDIRECT("1:"&-1+NETWORKDAYS(StartDate,EndDate,Holidays))),
Holidays))=DOW))

DOW = Day of week where Monday = 2, Thursday = 5

Holidays is a named range containing the holidays.

The formula generates an array of the workdays; we then see if they are equal to the desired DOW and count them.

Thank you Ron for your reply; truly appreciated.
The formula works very well except when I change the StartDate to 07/31/2013. Then I get a #REF! error in all my cells:

A1 Start Date: 07/01/2013
B2 End Date: 07/31/2013

A4 Holidays
A5 01/01/2013
A6 05/27/2013
A7 07/04/2013

When the Start Date is changed to 07/30/2013. It still works. See below.
Mon Tuesday Wednesday Thursday Friday
0 0 1 0 0

When the Start Date is changed to 07/31/2013, I get the #REF!
Mon Tuesday Wednesday Thursday Friday
#REF! #REF! #REF! #REF! #REF!

I tried removing the End Date from the formula but get the #VALUE!. I think the formula needs to be adjusted to only exclude holidays and the Start Date. My concern then is when the Start Date and End Date are the same, e.g 07/31/2013. What happens?

I also tested for the month August. Please see below:
When the Start Date is 08/29/2013, the formula works.

A1 StartDate 8/29/2013
B1 EndDate 8/31/2013
Mon Tuesday Wednesday Thursday Friday
0 0 0 0 1

When the Start Date is 08/30/2013, I get the #REF! error.
A1 StartDate 8/30/2013
B1 EndDate 8/31/2013

Mon Tuesday Wednesday Thursday Friday
#REF! #REF! #REF! #REF! #REF!

Thank you in advance for all your help and patience.

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.

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

On Wed, 24 Jul 2013 21:10:30 +0100, Addatone wrote:

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".

OK, that is how the formula is designed.


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!

Glad to help; thanks for the feedback.


In regards to your question, a Start Date can never be on a Holiday or
weekend. It always has to be a Business Day.

In that case, no change is required for the formula.