Counting Mondays in a date range minus holidays

[lows]

I need to be able to count the number of Mondays in a date range and subtract the Mondays which are holidays. By reading this message board I have found a nice way to count the number of Mondays in a date range:

Let A1 contain the start date and A2 contain the end date, then:

=SUM(1*(WEEKDAY(ROW(INDIRECT($A$1&":"&$A$2)))=2))

will give me the number of Mondays. I am struggling with how to count the number of Monday holidays from a list. If the list of holidays is contained in B1:B10, then I was hoping that something like the following would work:

=COUNTIF(B1:B10, ROW(INDIRECT($A$1&":"&$A$2)))

but it doesn't. Note, these are both array formulas and I am running Excel 2007.

I appreciate any guidance.

-Dean

 

[jasonb75]

Welcome to the board!

Are the holidays listed in B1:B10 all mondays? If they are then try

=SUM(COUNTIF(B1:B10, ROW(INDIRECT($A$1&":"&$A$2))))

for your second formula,

otherwise try,

=SUM(IF(WEEKDAY(B1:B10)=2,--ISNUMBER(MATCH(B1:B10,ROW(INDIRECT(A1&":"&A2)),0))))

both are array formula.

 

[lows]

Brilliant! Your second formula works perfectly. I'm sorry I didn't mention that the list of holidays could be other than Mondays.

Thank you!

-Dean

 

[santy71]

Hi, i'm using excel for mac 2011, and the formula =SUM(COUNTIF(B1:B10, ROW(INDIRECT($A$1&":"&$A$2)))) is giving me error. Can you please upload a file with your formula to see what i'm doing wrong? thanks.

 

[lows]

Make sure you are creating array formulas (i.e. CTRL+SHIFT+ENTER)

Otherwise, the formula you list looks good. Below is the actual code from my spreadsheet:

E16 contains the start date
F16 contains the end date
'Base figures'!$E$32:'Base figures'!$E$68 is the array of school holidays


Calculates the number of Mondays between start date and end date:
{=SUM(1*(WEEKDAY(ROW(INDIRECT($E$16&":"&$F$16)))=2))}


Calculates the number of Monday school holidays between begin date and end date:
{=SUM(IF(WEEKDAY('Base figures'!$E$32:'Base figures'!$E$68)=2,--ISNUMBER(MATCH('Base figures'!$E$32:'Base figures'!$E$68,ROW(INDIRECT($E$16&":"&$F$16)),0))))}

Good Luck.

 

[barry houdini]

Hi, i'm using excel for mac 2011, and the formula =SUM(COUNTIF(B1:B10, ROW(INDIRECT($A$1&":"&$A$2)))) is giving me error. Can you please upload a file with your formula to see what i'm doing wrong? thanks.

What are you trying to do exactly? If you have excel for mac 2011 you can use NETWORKDAYS.INTL function and that will allow you to use a simpler formula, e.g. to count Mondays between A2 and B2 excluding holidays listed in H2:H10

=NETWORKDAYS.INTL(A2,B2,"0111111",H$2:H$10)