Counting occurrences on a particular date

[Guest]

I'm trying to figure out how many documents my office processes in a
particular month. The spreadsheet has columns with document names and the
dates the documents were processed. Is there any way to create a formula that
will be able to count the number of occurrences of dates between, say
01/01/2007 and 01/31/2007 and give me that number so I can tell how many
permits were processed during the month?

All ideas *greatly* appreciated!

 

[Guest]

Assumes your dates are in column A, try this

=COUNTIF(A:A,"<31/1/2007")-COUNTIF(A:A,"<1/1/2007")

Mike

 

[Guest]

Thanks Mike,
Yes, the dates are all in a column. The formula you suggested returns a
date: 1/0/1900. I have a feeling I have to convert the dates to DateValue
before this will work, but I can't seem to figure out how to to a 'less
than', 'greater than' kind of thing with the DateValue formula.... I'm
hoping to be able to nest these functions somehow, because I don't want to
have to come up with a separate column to hold the converted dates. It'll
just confuse the folks who have to actually work with and maintain the
spreadsheet.

Sandra

 

[T. Valko]

Try one of these:

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

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

=INDEX(FREQUENCY(A1:A25,C11-{1,0}),2)

=SUMPRODUCT(--(A1:A25>=C1),--(A1:A25<=D1))

Biff

 

[Guest]

The formula doesn't return a date of any kind it counts the number of dates
before 31/1/2007 and subtracts from that the number of dates before 1/1/2007
and returns a number. The formula should be in a cell formatted as general.

How you view Excel dates has nothing to do with being able to do do
arithmetic on them. They are stored as a number no matter what the format and
there is no need to convert them.

Mike

 

[Guest]

If dates are text and there is only one year then:

=SUMPRODUCT(--(MONTH(DATEVALUE(A1:A200))=1))

will give counts for January

For a specific year:

=SUMPRODUCT(--(MONTH(DATEVALUE(A1:A200))=1),(--(Year(DATEVALUE(A1:A200))=2007)))

Remove DATEVALUE if date formats.

HTH

 

[Guest]

Try this:

=SUMPRODUCT(--(TEXT(A1:A100,"m/yy")="1/07"))