Count the number of VALID dates in range

**diesellam** · 10-12-2007, 12:20 PM

I did a search in google and found this formula that's supposed to work:
(Please see the following)

To count dates within a range (inclusive):

B1 = start date = 1/1/2006
C1 = end date = 7/1/2006

=COUNTIF(A1:A10,">="&B1)-COUNTIF(A1:A10,">"&C1)

Then it should be:

=COUNTIF(A1:A10,">="&1/1/2006)-COUNTIF(A1:A10,">"&7/1/2006)

But this only counts the number of cells that has the date, but not based on the range, why it doesn't work??

**NBVC** · 10-12-2007, 12:23 PM

The dates should also be enclosed within the quotes when you are counting this way....

try

=COUNTIF(A1:A10,">=1/1/2006")-COUNTIF(A1:A10,">7/1/2006")

**diesellam** · 10-12-2007, 12:29 PM

Thanks for the prompt reply but no, it still didn't work.

Based on your response I think the formula should be revised to like this, right?
(If I want to find any dates that happen between 1/1/2006 thru 1/31/2006 in column A.)

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

Is there a way to upload a file for you guys to check it out?

**NBVC** · 10-12-2007, 12:35 PM

Originally Posted by diesellam

Thanks for the prompt reply but no, it still didn't work.

Based on your response I think the formula should be revised to like this, right?
(If I want to find any dates that happen between 1/1/2006 thru 1/31/2006 in column A.)

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

Is there a way to upload a file for you guys to check it out?

I just double-checked and my original formula should work....

It looks for dates between Jan 1, 2006 and July 1, 2006.. maybe you had the dates mixed up and should've been 1/7/2006?

to upload, zip the file and attach it here by clicking the paperclip icon in the reply window....

**diesellam** · 10-12-2007, 12:42 PM

Please see the attached file.

The cell that has the box border shows where your suggested formula is inserted. I want to do a search to see how many times for each month it happens from column E.

Thanks once again.

**NBVC** · 10-12-2007, 12:47 PM

The formula then should be:

=COUNTIF(E:E,">=10/1/2005")-COUNTIF(E:E,">10/31/2005")

you had <= in second Countif and you had 2006 as the year in the second Countif... I get a result of 13. Right?

**NBVC** · 10-12-2007, 12:51 PM

There is another way, if you want to create an easily listing of counts for each month of each year...see attached.

I listed the months/years and then used the formula: =SUMPRODUCT(--(TEXT($E$9:$E$2576,"mmmyy")=TEXT(B8,"mmmyy"))) copied down.

The only dilemma is you have to used a fixed range with Sumproduct...not allowed whole range like E:E...

**diesellam** · 10-12-2007, 12:52 PM

COOL, it works. Thanks a lot.
My question is:

If the dates happen to be between 10/1/2005 and 10/31/2005, I understand the >=10/1/2005 because it's equal or after 10/1/2005, but how come the end is >10/31/2005??? > means after 10/31/2005? Why is it not <= ?? (equal or before)

=COUNTIF(E:E,">=10/1/2005")-COUNTIF(E:E,">10/31/2005")

**NBVC** · 10-12-2007, 01:01 PM

Originally Posted by diesellam

COOL, it works. Thanks a lot.
My question is:

If the dates happen to be between 10/1/2005 and 10/31/2005, I understand the >=10/1/2005 because it's equal or after 10/1/2005, but how come the end is >10/31/2005??? > means after 10/31/2005? Why is it not <= ?? (equal or before)

=COUNTIF(E:E,">=10/1/2005")-COUNTIF(E:E,">10/31/2005")

The first part counts all dates greater than or equal to Oct 1, 2005... in your list everything after and including row 12

The second counts all dates greater that October 31, 2005 ... in your list everthing after row 25

If you subtract the total number of dates greater than Oct 31 from the number of dates greater than or equal to Oct 1, you get the number of dates between Oct 1 and Oct 31.

**diesellam** · 10-12-2007, 01:07 PM

I tested your other method, I think that is more suitable to what I want. (The SUMPRODUCT method.)

It's interesting this doesn't work the whole column, but I think I can tweek it by adding the biggest arbitrary number, then I think it still works, right?

**NBVC** · 10-12-2007, 01:25 PM

Originally Posted by diesellam

I tested your other method, I think that is more suitable to what I want. (The SUMPRODUCT method.)

It's interesting this doesn't work the whole column, but I think I can tweek it by adding the biggest arbitrary number, then I think it still works, right?

Right. Just make sure to make the ranges absolute by adding the $ signs as per my sample...