Count the remaining weekdays in a data range excluding holidays and start/end dates
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
|