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

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?

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.

• The reason the `A:A` formula does not work is that the blank cells return `7` which is counted by the formula. Instead try this: `{=SUM(N(WEEKDAY(A:A,2)<6))}`. Though I am afraid it only work in Office 2013 and later. In previous versions, the WEEKDAY formula had different options and does not include for Numbers 1 (Monday) to 7 (Sunday) Jan 20 '15 at 1:10