Exclude weekends and holidays from a formula.

Hi Stevey,

Post a sample workbook.

Cheers,

P.S. Welcome to the forum!

Not entirely sure I follow... but based on my understanding you need to revise formulae in rows 35 & 36, ie:

C35: =SUMPRODUCT(--(WEEKDAY($B$4:$B$33,2)<6),--(C$4:C$33<>""))
C36: =SUMPRODUCT(--(WEEKDAY($B$4:$B$33,2)<6),--(C$4:C$33="Not in Use"))
both copied across to column J

Hi,

Forgive me for the lack of clarity but you nailed it and it works great but could that code be modified somehow, to exclude Bank Holidays as well? ie Xmas day, new years day etc

Regards, Stevey

Yes.

If we look at your existing NETWORKDAYS function:

C38: =NETWORKDAYS(B4,B33,L15:L18)

we can see you've assigned range L15:L18 to be the public holiday range (ie the range in which public holiday dates are to be listed) -- for the sake of demo let's continue with that being the range

C35: =SUMPRODUCT(--(WEEKDAY($B$4:$B$33,2)<6),--ISNA(MATCH($B$4:$B$33,$L$15:$L$18,0)),--(C$4:C$33<>""))
C36: =SUMPRODUCT(--(WEEKDAY($B$4:$B$33,2)<6),--ISNA(MATCH($B$4:$B$33,$L$15:$L$18,0)),--(C$4:C$33="Not in Use"))

In reality your public holiday range will be bigger (to encompass all public holidays) but you can revise ranges as and when - the above is just a demo of concept.