Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Subtracting holidays from a count of Saturdays in Excel 1

Status
Not open for further replies.

BJ9

MIS
Feb 10, 2006
54
US
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
 
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.
 
Thanks a lot John. That did the trick.

BJ
 
Glad to help.

[cheers]

[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.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top