MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Counting dates


Posted by Paul on January 04, 2002 6:34 PM

I need a formula to count how many times the dates in A2:A100 = the year 2001. Thanks


Posted by Tom Urtis on January 04, 2002 8:49 PM

Here is one way to solve this, with 2 formula choices.

In cell B1, enter the date 01/01/01.
In cell C1, enter the date 12/31/01.

In cell D1 or anywhere you want, enter either:
=SUMPRODUCT((A2:A100>=B1)*(A2:A100<=C1))

or array enter (that is, enter by simultaneously pressing Ctrl+Shift+Enter):
=SUM((A2:A100>=B1)*(A2:A100<=C1))

Either of those formulas will return the number of times a date in the range A2:A100 falls within the year 2001.

One note, array formulas are not familiar to most casual Excel users, so to keep things as simple as possible, all things being equal, you're probably better off with the first formula option.

Tom Urtis

Posted by Aladin Akyurek on January 05, 2002 1:57 AM

Tom --

Using the YEAR function, the formulas can be shortened.

> Here is one way to solve this, with 2 formula choices.

> In cell D1 or anywhere you want, enter either:
> =SUMPRODUCT((A2:A100>=B1)*(A2:A100<=C1))

=SUMPRODUCT((YEAR(A2:A100)=2001)+0)

where a cell can also be used if that cell houses the value 2001.

> or array enter (that is, enter by simultaneously pressing Ctrl+Shift+Enter):
> =SUM((A2:A100>=B1)*(A2:A100<=C1))

{=SUM((YEAR(A2:A100)=2001)+0)}

> Either of those formulas will return the number of times a date in the range A2:A100 falls within the year 2001.

Yep.

Aladin

=======

Posted by Tom Urtis on January 05, 2002 4:03 AM

Thank you Aladin, I toyed a little with how to do that last night but could not remember the syntax. Thanks again for the tip.

Tom U.

Posted by Paul on January 05, 2002 6:54 AM

Thanks for your help!