It's because 40087 is the serial number of 10/01/2009 (days since Jan 0
1900), 10/01/2009 as a number would look for the 40087th column in the table
where I assume if used as "40087" as a text expression apparently works. If
using a numerical number. If you make the date headers into text by for
instance using a formula like =TEXT(A5,"mm/dd/yyyy") and replace all headers
with that and then copy and paste special over the old headers so they are
text, then


DCOUNT(Headcount!C2:BZ125,"10/1/2009",Criteria!A1:B2)


will work

--


Regards,


Peo Sjoblom


"jco" wrote in message
...
Didn't work. The format of the cell does not seem to matter. The problem
is
in the formula itself. When I type
=DCOUNT(Headcount!C2:BZ125,"40087",Criteria!A1:B2)
it works, but when I type:

=DCOUNT(Headcount!C2:BZ125,"10/1/2009",Criteria!A1:B2)
or
=DCOUNT(Headcount!C2:BZ125,"Oct-09",Criteria!A1:B2)
I get #Value!

And I get the same results no matter what format I use in the database.


"Jacob Skaria" wrote:

Preformat the cell to general and instead of entering the date manually
try
and enter the date using Ctrl+ ; (semicolon) and then edit the date to
suit..

If this post helps click Yes
---------------
Jacob Skaria


"jco" wrote:

The problem does not seem to be so much on the database side as in the
formula. No matter what format I use in the database, the formula
returns
#value! if I don't use "40087" (for october 2009) in the formula
itself.

"Sean Timmons" wrote:

OK, try copying a blank cell, highlight the column, paste
special/Add, then
enter the data

"jco" wrote:

Yes, I did.

"Sean Timmons" wrote:

Have you formatted the column as date first?

"jco" wrote:

I am using Windows 2007. In my database, the date (month and
year) is a
column header. In the formula, if I enter the field in "" as
"10/1/2009", or
"Oct-09", I get #VALUE!. However, if I enter the field as
"40087", the
formula returns the correct result. Has anyone come up with
this and know a
way around? I can come up with the number equivalent of each
date, but since
I am not the end user of this file, it would be easier to be
able to enter
the date.