Count Date Range

[TClark14]

In a column I have a list of dates and I would like to count within that column range how many entries between Date 1 and Date 2. Could someone help me with this formula?

Thanks
Terry

[Mark W.]

How 'bout an array formula...

{=COUNT(IF((A2:A4>"1/31/02"+0)*(A2:A4<"3/1/02"+0),1))}


Note1: This is an "exclusive between". Replace ">" and "<" with ">=" and "<=" for an "inclusive between".

Note2: Array formulas must be entered using the Control+Shift+Enter key combination. The outermost braces, { }, are not entered by you -- they're supplied by Excel in recognition of a properly entered array formula.



[ This Message was edited by: Mark W. on 2002-04-03 10:48 ]

[Aladin Akyurek]

On 2002-04-03 10:30, TClark14 wrote:
In a column I have a list of dates and I would like to count within that column range how many entries between Date 1 and Date 2. Could someone help me with this formula?

Thanks
Terry

Also:

=COUNTIF(A1:A10,">="&E1)-COUNTIF(A1:A10,">"&E2)

where A1:A10 houses dates, E1 Date 1, E2 Date 2, and E2 > E1.

[IML]

also
=COUNTIF(A2:A4,">="&"1/31/02"+0)-COUNTIF(A2:A4,">"&"3/01/02"+0) for inclusive

or
=COUNTIF(A2:A4,">"&"1/31/02"+0)-COUNTIF(A2:A4,">="&"3/01/02"+0) for exclusive will do the trick.

That twice!
I will hit topic review before posting
I will hit topic review before posting
I will hit topic review before posting
I will hit topic review before posting
I will hit topic review before posting
I will hit topic review before posting
I will hit topic review before posting
I will hit topic review before posting
[ This Message was edited by: IML on 2002-04-03 11:11 ]

[ This Message was edited by: IML on 2002-04-03 11:12 ]

[TClark14]

I have tried entering all the above formulas as you have them and my result is always 1. I know positively I should get a count of 4 for the month of April. Could the date format have anything to do with the count I am getting?

[kinkyparamour]

Looking at the above formulas I would think you need to change the date to include April.......


Denny

[ This Message was edited by: kinkyparamour on 2002-04-03 12:09 ]

[TClark14]

I did change the dates in the above formulas to be >3/31/02 and less than 5/1/02, and also tried other dates, but I always get a count of 1.

[Aladin Akyurek]

On 2002-04-03 12:27, TClark14 wrote:
I did change the dates in the above formulas to be >3/31/02 and less than 5/1/02, and also tried other dates, but I always get a count of 1.

You could also put in criteria cells, say,

in E1: 4/1/02 and

in E2: =EOMONTH(E1,0) or just 4/30/02

in oder to specify the whole month April (of 2002) as criterion, and use the formula:

=COUNTIF(A1:A10,">="&E1)-COUNTIF(A1:A10,">"&E2)

That you get an unexpected count could be due to the formatting of the date range.

Check the dates as follows:

in B1 enter: =ISNUMBER(A1) and copy down. You should get only TRUE's in B. If not, your dates are probably "text", not true dates.