Count Date Range

TClark14

New Member
Joined
Apr 2, 2002
Messages
9
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
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,908
Members
448,532
Latest member
9Kimo3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top