Subtracting holidays from a count of Saturdays in Excel 1

[BJ9]

Hi all,

I have a formula that is counting Saturdays for me. The formula is: =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A18&":"&B18)))=7))

This is working well for me with one exception. I need it to NOT count the holidays that land on Saturday. I have a list of the holidays that I don't want it to count but I'm not sure how to put it into this formula.

I want something similar to this:
=NETWORKDAYS(A18,B18,'Holiday Dates'!A5:A34)
but for Satudays.

Thanks in advance
BJ

 

[AnotherHiggins]

Hey, BJ.

You'll need to find how may of the holidays that fall between your start and end dates are Saturdays. You can accomplish this with another SumProduct formula.

[COLOR=blue white]=SUMPRODUCT(('Holiday Dates'!A5:A34>A18)*('Holiday Dates'!A5:A34<B18)*--((WEEKDAY('Holiday Dates'!A5:A34))=7))[/color]

Once you have that solution, you can just subtract it from your existing count of Saturdays between the start and end dates.

So your final formula will look like this:
[COLOR=blue white]=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A18&":"&B18)))=7))-SUMPRODUCT(('Holiday Dates'!A5:A34>A18)*('Holiday Dates'!A5:A34<B18)*--((WEEKDAY('Holiday Dates'!A5:A34))=7))[/color]


[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[BJ9]

Thanks a lot John. That did the trick.

BJ

 

[AnotherHiggins]

Glad to help.



[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.