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
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. |
#5
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. |
#6
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. |
#7
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. |
#8
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 |