Count the remaining weekdays in a data range excluding holidays and start/end dates

[Addatone]

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 i
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

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

A1
=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 th
start date, the count for the remaining weekdays must excludes tha
start date Eg. On Tuesday, 07/30/2013, there should be 0 count for th
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 remainin
Wednesdays in the month of July.

Please help.

Thanks Addaton

 

[Ron Rosenfeld]

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.

 

[Addatone]

Thank you Ron for your reply; truly appreciated.
The formula works very well except when I change the StartDate t
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. Se
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!.
think the formula needs to be adjusted to only exclude holidays and th
Start Date. My concern then is when the Start Date and End Date are th
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.

;1612874']On Tue, 23 Jul 2013 22:32:50 +0100, Addaton

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 th remaining
Wednesdays in the month of July.

Please help.

Thanks Addatone-

Given a Start Date and End Date, the following formula will return th
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 ar
equal to the desired DOW and count them

 

[Ron Rosenfeld]

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,SUMPRODUCT(
--(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.

 

[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 an the
Start Date.[/i]

[/QUOTE]

I meant to say, "The formula needs to be adjusted to only exclud
holidays and the Start Date and not to exclude the End Date as
originally wrote".

The new formula you sent works perfectly and I'm no longer getting th
#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 o
weekend. It always has to be a Business Day.

Thanks once again for your help. You totally saved the day.

Later,
Addatone

;1612901']On Wed, 24 Jul 2013 16:27:53 +0100, Addaton

I tried removing the End Date from the formula but get the #VALUE!. - -
Ithink the formula needs to be adjusted to only exclude holidays an the
Start Date.-

Please provide an example where it is excluding other dates. It shoul
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 i
one or less WorkDays, an error will result.
A quick fix is to test for that condition:


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

However, there is another potential problem -- can StartDate ever be o
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

 

[Ron Rosenfeld]

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.