Function to count Number of weekday in a Month

[thuanpham]

Hi
I wonder if anybody knows if there is a function that shows number of weekday (from Mon to Fri) in a given month like in May 2005, there are 22 weekdays.
Thank you

[duane]

I strung 5/1 thru 5/31 in cells a1:a31, and this does it for you

=COUNT(IF(WEEKDAY(A1:A31,2)<6,A1:A31))

not sure how to do it without the list of dates

[Domenic]

Here's another way...

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(DATE(2005,5,1)&":"&DATE(2005,5,31))),2)<6))

OR

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&B1)),2)<6))

...where A1 contains your start date, and B1 contains your end date.

Hope this helps!

[thuanpham]

Thank you for your help. I found out that with the use of formula to calculate first date and last date of the month, we can do this formula for the current month like this and it works great.
=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(DATE(YEAR(TODAY()),MONTH(TODAY()),1)&":"&(DATE(YEAR(TODAY()), MONTH(TODAY())+1,1)-1))),2)<6))

This formula will give number of weekdays for Jan

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(DATE(YEAR(TODAY()),MONTH(1),1)&":"&(DATE(YEAR(TODAY()), MONTH(2)+1,1)-1))),2)<6))

[Domenic]

This formula will give number of weekdays for Jan

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(DATE(YEAR(TODAY()),MONTH(1),1)&":"&(DATE(YEAR(TODAY()), MONTH(2)+1,1)-1))),2)<6))

Actually, your formula should be...

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(DATE(YEAR(TODAY()),1,1)&":"&(DATE(YEAR(TODAY()),2,1)-1))),2)<6))

Otherwise...

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(DATE(YEAR(TODAY()),MONTH(1), 1)&":"&(DATE(YEAR(TODAY()),MONTH(12)+1,1)-1))),2)<6))

...will still give you 21.

Hope this helps!