Number of Mondays (Tuesdays, etc.) in a month, not counting holidays

Can you confirm which version of Excel you are using - your profile says Excel 2003, is that right?

It's easy with Excel 2010 or later, you can use NETWORKDAYS.INTL function, e.g. for Mondays

=NETWORKDAYS.INTL(A1,EOMONTH(A1,0),"0111111",holidays)

The "0111111" part gives you Mondays, change to "1011111" for Tuesdays, "1101111" for Wednesdays etc.

In Excel 2003, assuming you have Analysis ToolPak enabled you can use this version to exclude holidays

=SUMPRODUCT((WEEKDAY(ROW(INDIRECT(A1&":"&EOMONTH(A1,0))),2)=1)*ISNA(MATCH(ROW(INDIRECT(A1&":"&EOMONTH(A1,0))),holidays,0)))

Where the 1 is for Mondays, 2 for Tuesdays etc.

Originally Posted by ratkins

So is that 2nd formula a combination of the two Monday-counting formulas I posted? It seems to combine elements of both.

Yes, I rejigged it a little but this part.....

ROW(INDIRECT(A1&":"&EOMONTH(A1,0)))

creates an array of all dates in the month, and the WEEKDAY part tests those dates against the day of week and the ISNA(MATCH part tests the same array against the holiday range, SUMPRODUCT will count only those dates which are the correct weekday and aren't in the holiday range.

I'm assuming that A1 date is always the first of the month as that seems to be the case for your formulas

Edit: ....but I made a small error in the formula originally - the way I wrote it 1 would actually be equal to Sunday for the day check. I added ,2 to the WEEKDAY function to fix that - please use amended version

Originally Posted by ratkins

By the way, do people not use the formula tag?

Some people do and some don't.

I don't.

I find them to be a PITA in that with longer formulas you have to scroll to see the formula and the entire formula will not be in full view.