Counting Soecific dates in a range

T. Valko penned:
> Try this:

> =COUNTIF(A1:A1000,">="&DATE(2009,9,1))-COUNTIF(A1:A1000,">"&DATE(2009,9,30))

Returns a FALSE

> Or, use cells to hold the date boundaries:

> C1 = start date = 1/9/2009
> D1 = end date = 30/9/2009

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

Returns a FALSE
 >> Stay informed about: Counting Soecific dates in a range 

I think you changed Biff's formula. That's a minus that separates terms, not an
equal sign.

If that's not it, you may want to try Biff's suggestion once more. And if that
doesn't work, post your exact formula.

Steve wrote:
>
> T. Valko penned:
> > Try this:
>
> > =COUNTIF(A1:A1000,">="&DATE(2009,9,1))-COUNTIF(A1:A1000,">"&DATE(2009,9,30))
>
> Returns a FALSE
>
> > Or, use cells to hold the date boundaries:
>
> > C1 = start date = 1/9/2009
> > D1 = end date = 30/9/2009
>
> > =COUNTIF(A1:A1000,">="&C1)-COUNTIF(A1:A1000,">"&D1)
>
> Returns a FALSE
>
> --
> Steve
 >> Stay informed about: Counting Soecific dates in a range 

Dave Peterson penned:
> I think you changed Biff's formula. That's a minus that separates terms, not an
> equal sign.

> If that's not it, you may want to try Biff's suggestion once more. And if that
> doesn't work, post your exact formula.

> Steve wrote:

>> T. Valko penned:
>>> Try this:

>>> =COUNTIF(A1:A1000,">="&DATE(2009,9,1))-COUNTIF(A1:A1000,">"&DATE(2009,9,30))

>> Returns a FALSE

>>> Or, use cells to hold the date boundaries:

>>> C1 = start date = 1/9/2009
>>> D1 = end date = 30/9/2009

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

>> Returns a FALSE

This is the exact formula (copy and paste):

=COUNTIF(K3:K1000,">="&DATE(2009,9,1))-COUNTIF(K3:K1000,">"&DATE(2009,9,30))

You will note the only changes to original are I'm looking at a range
K3:K1000, sadly it still returns a FALSE. Would the fact that some cells
in the range are not populated i.e "blank"?

Thanks for your continuing help
 >> Stay informed about: Counting Soecific dates in a range 

I can't reproduce your error with my data; you formula work OK. If your dates
were text values then this would produce a #VALUE! error not return a True
False answer.

If you would like to send the workbook to peter_atherton at hotmail dot com

replacing the at with @ and dot with . I'll try and see what's going on.

Regards
Peter

"Steve" wrote:

> Dave Peterson penned:
> > I think you changed Biff's formula. That's a minus that separates terms, not an
> > equal sign.
>
> > If that's not it, you may want to try Biff's suggestion once more. And if that
> > doesn't work, post your exact formula.
>
> > Steve wrote:
>
> >> T. Valko penned:
> >>> Try this:
>
> >>> =COUNTIF(A1:A1000,">="&DATE(2009,9,1))-COUNTIF(A1:A1000,">"&DATE(2009,9,30))
>
> >> Returns a FALSE
>
> >>> Or, use cells to hold the date boundaries:
>
> >>> C1 = start date = 1/9/2009
> >>> D1 = end date = 30/9/2009
>
> >>> =COUNTIF(A1:A1000,">="&C1)-COUNTIF(A1:A1000,">"&D1)
>
> >> Returns a FALSE
>
> This is the exact formula (copy and paste):
>
> =COUNTIF(K3:K1000,">="&DATE(2009,9,1))-COUNTIF(K3:K1000,">"&DATE(2009,9,30))
>
> You will note the only changes to original are I'm looking at a range
> K3:K1000, sadly it still returns a FALSE. Would the fact that some cells
> in the range are not populated i.e "blank"?
>
> Thanks for your continuing help
> --
> Steve
>
 >> Stay informed about: Counting Soecific dates in a range 

Billy Liddel penned:
> I can't reproduce your error with my data; you formula work OK. If your dates
> were text values then this would produce a #VALUE! error not return a True
> False answer.

Hi Billy,

I copied the data out of the current SS to create a new one to send to
you, I reapplied the formula and it worked as expected, so I have now
copied and pasted this new column back into the original SS and all
works as expected?????

I'm scratching my head here as to what the problem was as I have double
and triple checked the data for text, I've reformatted the column
several times to ensure its the correct 'Date Format'.

Thanks for your help and suggestions
 >> Stay informed about: Counting Soecific dates in a range 

I still think that you had an equal sign instead of a minus sign (that would
explain how you got a false returned).

Just a note on changing the format of the cells. That's not enough to change a
text value to a real date. You have to do more. Even hitting F2, then enter is
enough (after the format change).

Steve wrote:
>
> Billy Liddel penned:
> > I can't reproduce your error with my data; you formula work OK. If your dates
> > were text values then this would produce a #VALUE! error not return a True
> > False answer.
>
> Hi Billy,
>
> I copied the data out of the current SS to create a new one to send to
> you, I reapplied the formula and it worked as expected, so I have now
> copied and pasted this new column back into the original SS and all
> works as expected?????
>
> I'm scratching my head here as to what the problem was as I have double
> and triple checked the data for text, I've reformatted the column
> several times to ensure its the correct 'Date Format'.
>
> Thanks for your help and suggestions
>
> --
> Steve
>
> First things first, but not necessarily in that order.
> - Doctor Who
 >> Stay informed about: Counting Soecific dates in a range