Mondays to Fridays Grouped by Month, Excluding Holidays, Between Two Dates

Hi Excel Forum,

As you can see by the description of this thread, I'm in need of a tricky excel formula.

I would like to work out the number of weekdays (Mondays to Fridays), grouped in each month (January to December), excluding holidays.
The values should be calculated from two dates e.g 02/05/2015 - 29/06/2015

Perhaps the following table will give a bit more insight:

		Monday	Tuesday	Wednesday	Thursday	Friday
2015	January
2015	February
2015	March
2015	April
2015	May
2015	June
2015	July
2015	August
2015	September
2015	October
2015	November
2015	December

I've attached an Excel Workbook with an example and the holidays date range.
Workdays, in months, excluding holidays, between two dates.xlsx

Hi Glenn,

Thank you for the reply. I appreciate the attempted help.

This formula got a bit complicated for me too when I had to start grouping it by months.
I tried using a PivotTable to produce the results, but to no avail.

I've searched the internet high and low for this answer, hopefully somebody else may be able to help.

Originally Posted by nflsales

See the attached file
Try the below array formula in C2 and copy across
=SUMPRODUCT((TEXT(DATE(2015,1,1)+ROW($1:$365)-1,"MMMM")=$B2)*(TEXT(DATE(2015,1,1)+ROW($1:$365)-1,"DDDD")=C$1)*(MMULT((DATE(2015,1,1)+ROW($1:$365)-1>=TRANSPOSE($A$16:$A$21))*(DATE(2015,1,1)+ROW($1:$365)-1<=TRANSPOSE($B$16:$B$21))*1,ROW($A$16:$A$21))>0)*(MMULT((DATE(2015,1,1)+ROW($1:$365)-1<>TRANSPOSE($A$24:$A$35+0))*1,(ROW($A$24:$A$35)>0)*1)=COUNTA($A$24:$A$35)))

Please Login or Register  to view this content.

Hi NFLSales,

I am really impressed with the above formula. Repped!

However, I see that the formula does not work when trying to use it on a range that spans from 2014 to 2015 (should probably have mentioned it, sorry).

How would I adapt the formula to work with dates such as 26/12/2014 - 13/01/2015?

Regards.