Help - Function Count, but on dates not numbers

[Nadine]

Hi, not sure if I can get help here on this are not but.....

Trying to create a formula where it counts the number of records that is
greater then .... but less then....
Problem is I know how to do this if the cells contain numbers. But can't
figure out how to do the formula where the cells/columns have DATEs as it's
contents.

Want the formula to - count the number of records that have dates that are
greater then "specific date" and less then "specific date". And return the
results of how many records meet that criteria.

Thanks if any one can help.
Nadine

 

[T. Valko]

Try one of these:

When you say:

greater then "specific date" and less then "specific date".

I'm assuming you do not want to include the start date and the end date.

=COUNTIF(A1:A20,">"&DATE(2007,7,1))-COUNTIF(A1:A20,">="&DATE(2007,7,31))

Or:

C1 = 7/1/2007
D1 = 7/31/2007

=COUNTIF(A1:A20,">"&C1)-COUNTIF(A1:A20,">="&D1)

If you *do* want to include the start date and end date:

=COUNTIF(A1:A20,">="&DATE(2007,7,1))-COUNTIF(A1:A20,">"&DATE(2007,7,31))

=COUNTIF(A1:A20,">="&C1)-COUNTIF(A1:A20,">"&D1)

 

[Ron Rosenfeld]

Hi, not sure if I can get help here on this are not but.....

Trying to create a formula where it counts the number of records that is
greater then .... but less then....
Problem is I know how to do this if the cells contain numbers. But can't
figure out how to do the formula where the cells/columns have DATEs as it's
contents.

Want the formula to - count the number of records that have dates that are
greater then "specific date" and less then "specific date". And return the
results of how many records meet that criteria.

Thanks if any one can help.
Nadine

=countif(rng,">="&StartDt) - countif(rng,">"&EndDt)


--ron

 

[KellyF]

Hi,

Wondering if you might be able to help me with a similar question?

I've got a column (D) which has dates that a request was approved.

I want to calculate how many requests were approved per month over a two
year period.

Is there a away to do this other than using "less than" and "greater than"
for each month?

Thanks!

 

[Ron Rosenfeld]

Hi,

Wondering if you might be able to help me with a similar question?

I've got a column (D) which has dates that a request was approved.

I want to calculate how many requests were approved per month over a two
year period.

Is there a away to do this other than using "less than" and "greater than"
for each month?

Thanks!

You can probably do that with a Pivot Table
--ron

 

[Nadine]

tried the formula:

=COUNTIF(A1:A20,">"&DATE(2007,7,1))-COUNTIF(A1:A20,">="&DATE(2007,7,31))

But the results provide an incorrect count.

We have a column with multiple dates over 3 years. But want to only count
how many records there are that are between the dates 01-APR-07 and 31-MAR-08.
If count manually should get 41 records, but calculation gives results of
37.

Thanks

 

[Peo Sjoblom]

Try

=COUNTIF(A1:A20,">="&DATE(2007,4,1))-COUNTIF(A1:A20,">"&DATE(2008,3,31))


--


Regards,


Peo Sjoblom

 

[Nadine]

Further to post below - looks like the results is doing a subtraction an
given the balance. But not wanting a subtraction, but a count of how many
records are within that date frame.

Thanks

 

[T. Valko]

That particular formula will not count the start and end dates.

This "problem" arises quite often when someone makes a post and says:

Count *between* x and y when they meant to say count *from* x to y.

For example, if I say count the numbers between 1 and 10 that literally
means count 2,3,4,5,6,7,8,9.

So, to count the numbers *from* 1 to 10:

=COUNTIF(A1:A20,">=1")-COUNTIF(A1:A20,">10")

To count the numbers *between* 1 AND 10:

=COUNTIF(A1:A20,">1")-COUNTIF(A1:A20,">=10")

 

[Nadine]

THANKS!!!!! got it now, its working. happy happy. thanks.

 

[T. Valko]

You're welcome. Thanks for the feedback!