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?
Best 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,
the press Ctrl+Shift+Enter to make it an array formula.
The end resulting formula will look like this.
Note: The column reference
A:A
will not work. You have to give the actual range.