worksheet function - Excel: How to count cells, whose date values represent a day between monday and friday?

Friday 2 March 2018

I want to count cells, whose values are dates, under the following condition: The date value entered must be a day between Monday and Friday.

Assume the following data and my try:

Column A
--
15.01.2015 (5)
16.01.2015 (6)
17.01.2015 (7)
18.01.2015 (1)
19.01.2015 (2)

The function WEEKDAY seems to be practical to use. It returns 1 for a Monday through 7 for a Sunday.

Formula: =COUNTIF( A:A; WEEKDAY(A:A; 1) < 6 )

For now my formula returns 0, but 3 is expected.

How can I change it to the right behavior?

Answer

I don't think COUNTIF will be able to do what you want without the use of a helper column. So, give the following array formula a try.

Enter this formula,

=SUM(N(WEEKDAY(A1:A5)>6))

the press Ctrl+Shift+Enter to make it an array formula.

The end resulting formula will look like this.

{=SUM(N(WEEKDAY(A1:A5)>6))}

Note: The column reference A:A will not work. You have to give the actual range.

Notes