1

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?

1

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.

1
  • 1
    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)
    – wbeard52
    Jan 20 '15 at 1:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.