# Count working days by week

I

#### inta251

Column A Column B
Date Week Number
Mon, Jan 08, 2007 2 =WEEKNUM(A2,1)
Mon, Jan 08, 2007 2 =WEEKNUM(A3,1)
Mon, Jan 08, 2007 2 =WEEKNUM(A4,1)
Tue, Jan 09, 2007 2 =WEEKNUM(A5,1)
Tue, Jan 09, 2007 2 and so on
Tue, Jan 09, 2007 2
Wed, Jan 10, 2007 2
Wed, Jan 10, 2007 2
Wed, Jan 10, 2007 2
Wed, Jan 10, 2007 2
Wed, Jan 10, 2007 2
Thu, Jan 11, 2007 2
Thu, Jan 11, 2007 2
Thu, Jan 11, 2007 2
Thu, Jan 11, 2007 2
Fri, Jan 12, 2007 2
Fri, Jan 12, 2007 2
Sun, Jan 14, 2007 3
Sun, Jan 14, 2007 3
Sun, Jan 14, 2007 3
Mon, Jan 15, 2007 3
Mon, Jan 15, 2007 3
Mon, Jan 15, 2007 3
Mon, Jan 15, 2007 3
and so on

So now I've to count working days in the weeks 1, 2, 3, ..........53
Same date entry as 1 day.
Something similar to count by month:
=SUMPRODUCT(--(YEAR(trips!\$A\$2:\$A\$1200)=2007),(MONTH(trips!\$A\$2:\$A\$1200)=1)
/COUNTIF(trips!\$A\$2:\$A\$1200,trips!\$A\$2:\$A\$1200&""))

D

#### Domenic

Assuming a five day work week (Monday through Friday), let D2, D3, D4,
etc., contain 1, 2, 3, etc., then try...

E2, copied down:

=SUMPRODUCT(--(WEEKDAY(\$A\$2:\$A\$25,2)<6),--(\$B\$2:\$B\$25=D2))

To exclude holidays, let a range of cells contain a list of holidays,
let's say G2:G10, then try...

=SUMPRODUCT(--(WEEKDAY(\$A\$2:\$A\$25,2)<6),--ISNA(MATCH(\$A\$2:\$A\$25,\$G\$2:\$G\$1
0,0)),--(\$B\$2:\$B\$25=D2))

Hope this helps!

I

#### inta251 via OfficeKB.com

Thanks for quick respond.
In both cases formulas count number of entrees in week 1, 2,â€¦.53.
I need count days per week in Column A
Now Iâ€™ll try simple. In Column A I have only dates.
Same dates entrees can be from 1 to infinity.

Sample: In this case
Date
01/08/2007
01/08/2007
01/08/2007 1st day
01/09/2007
01/09/2007
01/09/2007 2nd day
01/10/2007
01/10/2007
01/10/2007
01/10/2007
01/10/2007 3rd day
01/11/2007
01/11/2007
01/11/2007
01/11/2007 4th day
01/12/2007
01/12/2007 5th day
and so on

All this days belong to Week #2
This meet i worked 5 days in week 2.
Formula need with result 5.
Next Week #3,........53 (end of the year)
Thanks again. Igor.

D

#### Domenic

Assumptions:

A2:A100 contains the data

B2:B100 contains the week number

Formula:

=SUM(IF(FREQUENCY(IF(\$B\$2:\$B\$100=D2,\$A\$2:\$A\$100),IF(\$B\$2:\$B\$100=D2,\$A\$2:\$
A\$100)),1))

....where D2 contains the week number. Note that the formula needs to be
confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

I

#### inta251 via OfficeKB.com

Still incorrect formula.
Temporarily I got formula
Week 1:
=SUMPRODUCT(--(\$A\$2:\$A\$100>=DATE(2007,1,1)),--( \$A\$2:\$A\$100<=DATE(2007,1,6))
/COUNTIF(\$A\$2:\$A\$100,\$A\$2:\$A\$100&""))

Week 2:
=SUMPRODUCT(--(\$A\$2:\$A\$100>=DATE(2007,1,7)),--( \$A\$2:\$A\$100<=DATE(2007,1,13))
/COUNTIF(\$A\$2:\$A\$100,\$A\$2:\$A\$100&""))

and so on.

If you will have some idea, please let me know.
Sincerely, Igor.

D

#### Domenic

I don't understand. Can you elaborate?

I

#### inta251 via OfficeKB.com

Hi, Domenic.
I have dates in ColumnA. I need count dates I worked in the week.
=WEEKNUM(A1) ------- result week#2
.............................................
Same dates count as one day.
All this dates belong to week #2, and in the formula I need result 5 working
days
row1 01/08/2007
row2 01/08/2007
row3 01/08/2007 1st day
row4 01/09/2007
row5 01/09/2007
row6 01/09/2007 2nd day
row7 01/10/2007
row8 01/10/2007
row9 01/10/2007
row10 01/10/2007
row11 01/10/2007 3rd day
row12 01/11/2007
row13 01/11/2007
row14 01/11/2007
row15 01/11/2007 4th day
row16 01/12/2007
row17 01/12/2007 5th day
and so on

I

#### inta251 via OfficeKB.com

Hi, Domenic.
Sorry for my dummies, but after few hours of thinking I realized formula
needs to be
confirmed with CTRL+SHIFT+ENTER, not just ENTER. After that in front and end
of the formula
show up two symbols { and }. After correct confirmation (CTRL+SHIFT+ENTER) I
got satisfied result.
Once again, thanks for your time and help to â€˜DUMMIESâ€™.

=SUM(IF(FREQUENCY(IF(\$B\$2:\$B\$100=D2,\$A\$2:\$A\$100),IF(\$B\$2:\$B\$100=D2,\$A\$2:\$
A\$100)),1))