COUNTIF year of date is...

Question

Level 1

0 points

COUNTIF year of date is...

I'd like to count all the entries in my table whose date column includes the year 2009. However, the dates in this column are formatted like this: 12/07/09

How can I count only entries in 2009?

Mac OS X (10.6.2)

Posted on Dec 7, 2009 12:39 PM

Reply

Answer 1

Best reply

Badunit

Level 7

25,784 points

Dec 7, 2009 1:02 PM in response to rrex

Would this do it for you:

=COUNTIFS(B,"<1/1/2010",B,">12/31/2008")

EDIT: The solution above is the straightforward idea. Yvan's solution below solves any localization problems related to date formats in different countries.

Message was edited by: Badunit

Reply

Answer 2

KOENIG Yvan

Level 8

41,807 points

Dec 7, 2009 12:56 PM in response to rrex

Assuming that the dates are in column A, I would create an auxiliary column whose cells will contain the formula

=YEAR(A)
So it will be easy to get the count.

An other soluce would be to drop the auxiliary column but use the COUNTIFS() function :

In cell D1 insert the number of the year to test
D1 : 2009
in D2 insert : =DATE(D$1,1,1)
in D3 insert : =DATE(D$1+1,1,1)
in D4 insert : =COUNTIFS(A,">="&D2,A,"<"&D3)

Yvan KOENIG (VALLAURIS, France) lundi 7 décembre 2009 21:56:38

Reply