COUNTIF with dates

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jock - 18 Jun 2007 17:12 GMT

Dates in a column are formatted thus: 18/06/2007.
How can I adapt the COUNTIF formula to look at these dates (going back to
1986) and report back on the year part?
Any ideas?

Signature

tia

Jock

Reply to this Message

JE McGimpsey - 18 Jun 2007 17:20 GMT

What do you expect COUNTIF() to report?

If you want to count the number of dates in 1998, one way:

=COUNTIF(A:A,"<"&DATE(1999,1,1))-COUNTIF(A:A,"<"&DATE(1998,1,1))

or

=SUMPRODUCT(--(YEAR(A1:A10000)=1998))

> Dates in a column are formatted thus: 18/06/2007.
> How can I adapt the COUNTIF formula to look at these dates (going back to
> 1986) and report back on the year part?
> Any ideas?

Reply to this Message

Jock - 18 Jun 2007 17:35 GMT

I would like to know how many times '1998' appears in the list (and so on for
the other years too), but the problem is where there is text in a cell as
well as a date that the formula cames back with #value

Signature

tia

Jock

> What do you expect COUNTIF() to report?
>
[quoted text clipped - 10 lines]
> > 1986) and report back on the year part?
> > Any ideas?

Reply to this Message

Lori - 18 Jun 2007 20:31 GMT

If there's text too maybe try array-entered (using ctrl+shift+enter)
eg:

=COUNT(FIND(1998,A$1:A$100))

> I would like to know how many times '1998' appears in the list (and so on for
> the other years too), but the problem is where there is text in a cell as
[quoted text clipped - 20 lines]
> > > 1986) and report back on the year part?
> > > Any ideas?

Reply to this Message

Max - 18 Jun 2007 17:28 GMT

One guess ..

Something like this in say, B1:
=SUMPRODUCT(--(YEAR(A1:A100)=1987))
will count the number of dates within A1:A100 with the year 1987
(dates within A1:A100 are assumed real dates)

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

> Dates in a column are formatted thus: 18/06/2007.
> How can I adapt the COUNTIF formula to look at these dates (going back to
> 1986) and report back on the year part?
> Any ideas?

Reply to this Message