Count work days only

[johnbird1988]

Hello,

I am trying to count the number of days that fall into a working week (Mon-Fri). My dates are in range J6:NK6 and I want to count the number of times "S" appears in range J10:NK10 that is on a weekday only. But here is the tricky part I also need to exclude bank holidays that are in a named range called "Holidays".

So far I have managed to get the counting of the working week by using:

=SUMPRODUCT((WEEKDAY($J6:$NK6,2)<6)*($J10:$NK10="S"))

However I cannot get it to also not included bank holidays. I have tried

=SUMPRODUCT((WEEKDAY($J6:$NK6,2)<6)*($J10:$NK10="S")*($J6:$NK6<>Holiday))

But with no luck.

I think I am on the correct lines, but just need some help with the last bit.

Thank you,

John

 

[SteveO59L]

have a look at networkdays()

 

[johnbird1988]

networkdays() will only count the number of days between two dates and exclude holidays. I want to count the number of "S" in the range and if a "S" is entered on a weekend or holiday then it not to be counted. networkdays will not do this.

 

[barry houdini]

Try this version

=SUMPRODUCT((WEEKDAY($J6:$NK6,2)<6)*($J10:$NK10="S")*(COUNTIF(Holiday,$J6:$NK6)=0))

....or it is possible to use NETWORKDAYS like this

=SUMPRODUCT(NETWORKDAYS($J6:$NK6+0,$J6:$NK6+0,Holiday)*($J10:$NK10="S"))

 

[owensy]

help

count working days excel including weekends formula - =NETWORKDAYS(I7,J7) currently using this - but it won't count he weekends ! can you help me modify to include weekends please

 

[barry houdini]

If you want to count all days just subtract one from the other, e.g.

=J7-I7

format result cell as number

Depending on your definition you may need to add 1 (is today until tomorrow 1 day or 2?)

 

[owensy]

Re: hi Count work days only This gives me a minus figure tho ?

If you want to count all days just subtract one from the other, e.g.

=J7-I7

format result cell as number

Depending on your definition you may need to add 1 (is today until tomorrow 1 day or 2?)

hhh

 

[owensy]

Re: hi Count work days only This gives me a minus figure tho ?

This is giving a minus figure