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.
Quote:
Originally Posted by Ron Rosenfeld[_2_]
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.
|