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

  • Thread starter Thread starter jco
  • Start date Start date
J

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.
 
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
 
OK, try copying a blank cell, highlight the column, paste special/Add, then
enter the data
 
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.
 
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
 
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.
 
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
 
Thanks! Both solutions work.

Peo Sjoblom said:
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





.
Click to expand...
 
Back
Top