Count Date Entries Per Quarter

[Dvinechild]

Hola fellow Excel Kings & Queens~

Please let me know if there is a way to do the following:
Count the number of entries per quarter of data listed in a column.

I've thought about setting a >< of entries falling between set date
criteria, but unsure how to do this. Column is currently formatted in
mm-dd-yy. I don't understand enough about converting date info so that I
might get the infor I'm after, but I have a hunch that I might have to go
down that road.

Equation can be seperated into 4 seperate equations to keep things
simplified, the data is just ongoing and the results get put into
graphs/charts.

Your help is appreciated.

 

[T. Valko]

Try this:

Use cells to hold your date boundaries:

C1 = start date
D1 = end date

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

 

[Mike H]

Hi,

If i've understood correctly and with your dates in column A put this in a
cell and arrray enter and drag down 3 rows to sum Q1 to g4 dates

=COUNT(IF(INT((MONTH(A1:A100)/4)+1)=ROW(A1),IF(A1:A100<>"",A1:A100)))

This is an array formula which must be entered with CTRL+Shift+Enter and NOT
'just enter. If you do it correct then Excel will put curly brackets around
'the formula{}. You can't type these yourself. If you Edit the ranges
'then you must re-enter as An array

Mike

 

[Mike H]

oops,

needs to be absolute

=COUNT(IF(INT((MONTH($A$1:$A$100)/4)+1)=ROW(A1),IF($A$1:$A$100<>"",$A$1:$A$100)))

Mike

 

[Dvinechild]

Thanks Mike, I'm working on this but want to know if I can alter the equation
to read the entire column instead of seemingly stopping at row 100? I also
need to refer to another tab, do I enter that reference before the start of
the absolutes?

 

[Fred Smith]

Simply change the range to what's appropriate in your situation. If you want
the entire column, then use $A$1:$A$65535 (ie, change 100 to 65535, or
whatever your last row is).

Regards,
Fred.

 

[Dvinechild]

Thank you again.
The equation is not correctly stating the quantities however. The first qtr
is working fine,(36), but it is not counting the following qtrs correctly.
2nd qtr (dates fro 4/1 to 6/30), etc. are not being reflected accurately. My
sprdsht has 35 entries for 2nd qtr and using the equation, it's coming up
with 37.

Can you think of why? I've been trying all kinds of things but am not coming
up with the solution.

 

[Fred Smith]

How are you coming up with the formula for the 2nd quarter? You should be
dragging Mike's formula down one row to get the subsequent quarters. The 2nd
quarter formula should look like:
=COUNT(IF(INT((MONTH($A$1:$A$65535)/4)+1)=ROW(A2),IF($A$1:$A$65535<>"",$A$1:$A$65535)))

Regards,
Fred.

 

[Dvinechild]

Hi Fred,
I've done what both have you suggested, yet the count is not correct per
quarter. Would you be willing briefly look at my column of data? I have just
that info seperated out.

Kindly,
Justis

 

[Fred Smith]

Sure. Cut and paste it to a message in this group, or save your file to the
web, and let me know where it is.

Regards,
Fred.