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.