| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
DCOUNT - date as a field only works when entered as a number
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. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
DCOUNT - date as a field only works when entered as a number
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. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
DCOUNT - date as a field only works when entered as a number
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. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
DCOUNT - date as a field only works when entered as a number
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 "jco" wrote in message ... 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. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
DCOUNT - date as a field only works when entered as a number
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. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
DCOUNT - date as a field only works when entered as a number
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. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
DCOUNT - date as a field only works when entered as a number
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. |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
DCOUNT - date as a field only works when entered as a number
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. |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
DCOUNT - date as a field only works when entered as a number
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. |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
DCOUNT - date as a field only works when entered as a number
Thanks! Both solutions work.
"Peo Sjoblom" wrote: 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 "jco" wrote in message ... 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. . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Need to set up a number based on date entered | Excel Discussion (Misc queries) | |||
| Sumproduct based on date entered in separate field | Excel Discussion (Misc queries) | |||
| LIMIT THE NUMBER OF CHARACTERS ENTERED IN A FIELD | Excel Worksheet Functions | |||
| Date Calculation (from entered date / 1yr later in next field) | Excel Worksheet Functions | |||
| Date Entered +7, unless another field is greater than 0 | Excel Worksheet Functions | |||
Linear Mode
