DCOUNT - date as a field only works when entered as a number

[jco]

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.

 

[Sean Timmons]

Have you formatted the column as date first?

 

[jco]

Yes, I did.

Have you formatted the column as date first?

 

[Peo Sjoblom]

Post your full formula

If I put some dates in a column with a header called "Dates" and put Dates
in E2 and 10/01/09 in E3 then use this formula

=DCOUNT(A6:A30,"Dates",E2:E3)


I get the correct answer

--


Regards,


Peo Sjoblom

 

[Sean Timmons]

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

 

[jco]

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.

 

[Jacob Skaria]

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

 

[jco]

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.

 

[Peo Sjoblom]

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]

Thanks! Both solutions work.

Post your full formula

If I put some dates in a column with a header called "Dates" and put Dates
in E2 and 10/01/09 in E3 then use this formula

=DCOUNT(A6:A30,"Dates",E2:E3)


I get the correct answer

--


Regards,


Peo Sjoblom





.